这里your_mysql_name是分配给你的MySQL用户名,your_client_host是所连接的服务器所在的主机
[创建并选择数据库]
> create database user_sql;
> use user_sql
[在指定数据库里插入表]
> ceeate table <table_name> (field_name field_type,field_name field_type, field_name field_type);
[创建表]
> create
table
tabname(col1 type1 [
not
null
] [
primary
key
],col2 type2 [
not
null
],..)
[根据已有的表创建新表]
> A:
create
table
tab_new
like
tab_old (使用旧表创建新表)
> B:
create
table
tab_new
as
select
col1,col2…
from
tab_old definition
only
<2> 查询
[查询数据库里的表]
> show tables;
[查询表的样式] (describe->描述, table_name->表名)
>describe [sql.]table_name;
SELECT语句用来从数据表中检索信息。语句的一般格式是:
SELECT what_to_select [你想要看到的内容,可以是列的一个表,或*表示“所有的列”]
FROM which_table [从哪个表中查找]
[WHERE] conditions_to_satisfy; [如果选择该项,conditions_to_satisfy指定行必须满足的检索条件]
> select * from dog // * 代表所有列
-> where age = 15;
> select name, age from dog where age = 15;
[查询时合并相同的数据]
> select distinct name from dog;
<3> 插入
[从文本插入]
> load data local infile 'f:\dog.txt' into table dog lines terminated by '\r\n';(windows下插入)
> load data local infile 'f:\dog.txt' into table dog;(linux下插入)
[手动添加一条新记录]
> insert into dog values ("贝贝", 1, 20);[]
<4> 修改
[清空]
> delete from dog;
[修改单条记录]
> update dog set sex = 0 where name="beibei";
[可以添加多条判断]
> update dog set sex = 0 where name="beibei" and sex = 1;
> update dog set sex = 0 where name="beibei" or sex = 1;// and 优先级高于 or,可以加()区分
[增加一列]
> alter table tab_name add column col type;
<5> 分类
> select * from dog order by age; // 按年龄分类(order->秩序)
默认排序是升序,最小的值在第一。要想以降序排序,在你正在排序的列名上增加DESC(降序 )关键字:
> select * from dog order by age desc; // 降序排序(desc->说明,降序)
[当有两个条件时,只有第一个起作用]
> select * from dog order by age, sex desc;
<6> 删除
[删除一张表]
> drop
table
tabname
[删除整个数据库文件]
> drop
database
dbname
[删除表中所有的数据]
> delete from dog;
[一次删除多张表]
<7> 日期计算
> select name,birth,curdate, (year(curdate) - year(birth)) - (right(curdate
,5) < right(birth,5)) as age from pet;
[日期部分的提取函数]
YEAR( )、MONTH( )和DAYOFMONTH( )
mysql> select name, birth, month(birth) from pet;
+----------+---------------------+--------------+
| name | birth | month(birth) |
+----------+---------------------+--------------+
| | Fluffy | 1993-02-04 00:00:00 | 2 |
| | Claws | 1994-03-17 00:00:00 | 3 |
| | Buffy | 1989-05-13 00:00:00 | 5 |
| | Fang | 1990-08-27 00:00:00 | 8 |
| | Bowser | 1989-08-31 00:00:00 | 8 |