mysql命令详解

mysql命令详解

首先创建一个简单的user表

CREATE TABLE `user` (

`id` int NOT NULL AUTO_INCREMENT ,

`name` varchar(255) NULL ,

`sex` varchar(255) NULL ,

`age` int NULL ,

`address` varchar(255) NULL ,

PRIMARY KEY (`id`)

)

然后插入数据

insert into `user`(`name`,`sex`,`age`,`address`) values('张三','男',22,'北京') ;

insert into `user`(`name`,`sex`,`age`,`address`) values('李四','男',12,'河北') ;

insert into `user`(`name`,`sex`,`age`,`address`) values('王五','女',23,'江苏') ;

insert into `user`(`name`,`sex`,`age`,`address`) values('赵六','男',21,'北京') ;

1.修改某一个字段的名称(例如修改地址)

alter table user modify address varchar(50) ;

2.把user表中某些字段的值复制出来,变成另外一个表

create table user2 as select id,name from user ;

3.删除user表

drop table user [restrict|cascade] ;

4.创建表的时候加上外键约束

constraint ID_FK foreign key(id) references user2(id) ;

修改表的外键关系

alter table user add constraint ID_FK foreign key(id) references user2(id) ;

5.添加表的检查约束

alter table user add constraint CHK_SEX check(sex = '男') ;

alter table user add constraint CHK_SEX check(sex in ('男','女')) ;

6.从另一个表插入数据

insert into user (name) select name from user2 ;

事务是对数据库执行的一个操作单位。它是以逻辑顺序完成的工作单元或工作序列,无论是用户手工操作,还是由程序进行的自动操作。

控制事务的命令有3个

COMMIT ROLLBACK SAVEPOINT

首先把数据库默认的提交模式改变

set autocommit=0 禁止自动提交

set autocommit=1 开启自动提交

我们要从表user里删除剩余的数据,在进行每次删除之前都使用savepoint命令,这样就可以在任何时候利用rollback命令

回退到任意一个保存点,从而把适当的数据回复到原始状态。

下面是一个事务处理的完整例子:

**********************************

mysql> set autocommit=

0 ;

Query OK, 0 rows affected

mysql> delete from user where id=3 ;

Query OK, 1 row affected

mysql> select * from user ;

+----+------+-----+-----+---------+

id | name | sex | age | address |

+----+------+-----+-----+---------+

1 | 张三 | 男 | 22 | 北京 |

2 | 李四 | 男 | 12 | 河北 |

+----+------+-----+-----+---------+

2 rows in set

mysql> rollback ;

Query OK, 0 rows affected

mysql> select * from user ;

+----+------+-----+-----+---------+

id | name | sex | age | address |

+----+------+-----+-----+---------+

1 | 张三 | 男 | 22 | 北京 |

2 | 李四 | 男 | 12 | 河北 |

3 | 王五 | 女 | 23 | 江苏 |

+----+------+-----+-----+---------+

3 rows in set

mysql> savepoint sp1 ;

Query OK, 0 rows affected

mysql> delete from user where id=2 ;

Query OK, 1 row affected

mysql> savepoint sp2 ;

Query OK, 0 rows affected

mysql> delete from user where id=3 ;

Query OK, 1 row affected

mysql> savepoint sp3 ;

Query OK, 0 rows affected

mysql> delete from user where id=1 ;

Query OK, 0 rows affected

mysql> select * from user ;

Empty set

mysql> rollback to sp1 ;

Query OK, 0 rows affected

mysql> select * from user ;

+----+------+-----+-----+---------+

id | name | sex | age | address |

+----+------+-----+-----+---------+

1 | 张三 | 男 | 22 | 北京 |

2 | 李四 | 男 | 12 | 河北 |

3 | 王五 | 女 | 23 | 江苏 |

+----+------+-----+-----+---------+

3 rows in set

************************************

mysql> release savepoint sp1 ;

Query OK, 0 rows affected

这个命令用于删除创建的保存点。在某个保存点被释放之后,就不能再利用rollback命令来撤销这个保存点之后的

事务操作了。利用这个命令可以避免意外的回退到某个不再需要的保存点。

总结:commit用于把事务保存到数据库,rollback用于撤销已经执行了的事务,而savepoint用于把事务划分成组,

让我们可以回退到事务过程中特定的逻辑位置。在运行大规模事务操作时,应该经常使用commit和rollback命令来保证

数据库具有足够的剩余空间。另外还要记住,这些事务命令只用于3个DML命令:insert、update和delete。

查询语句

select all name from user 和 select name from user 用法一样,都是从表里查询出字段名为name的所有值

mysql> select distinct sex from user ;

+-----+

sex |

+-----+

男 |

女 |

+-----+

2 rows in set

查询出不重复的字段值distinct。

对查询出来的内容进行排序order by,默认是asc也就是升序。

mysql> select * from user order by age

+----+------+-----+-----+---------+

id | name | sex | age | address |

+----+------+-----+-----+---------+

2 | 李四 | 男 | 12 | 河北 |

1 | 张三 | 男 | 22 | 北京 |

3 | 王五 | 女 | 23 | 江苏 |

+----+------+-----+-----+---------+

3 rows in set

mysql> select * from user order by age asc

+----+------+-----+-----+---------+

id | name | sex | age | address |

+----+------+-----+-----+---------+

2 | 李四 | 男 | 12 | 河北 |

1 | 张三 | 男 | 22 | 北京 |

3 | 王五 | 女 | 23 | 江苏 |

+----+------+-----+-----+---------+

3 rows in set

mysql> select * from user order by age de

sc;

+----+------+-----+-----+---------+

id | name | sex | age | address |

+----+------+-----+-----+---------+

3 | 王五 | 女 | 23 | 江苏 |

1 | 张三 | 男 | 22 | 北京 |

2 | 李四 | 男 | 12 | 河北 |

+----+------+-----+-----+---------+

3 rows in set

排序还可以针对多个字段,也就是第一个指定的排序字段如果相等的话,比较第二个字段的值。以此类推。

mysql> select * from user order by age ;

+----+---------+-----+-----+---------+

id | name | sex | age | address |

+----+---------+-----+-----+---------+

2 | 李四 | 男 | 12 | hebei |

1 | 张三 | 男 | 22 | beijing |

3 | 王五 | 女 | 23 | jiangsu |

4 | wuhaixu | nan | 23 | hunan |

+----+---------+-----+-----+---------+

4 rows in set

mysql> select * from user order by age,address ;

+----+---------+-----+-----+---------+

id | name | sex | age | address |

+----+---------+-----+-----+---------+

2 | 李四 | 男 | 12 | hebei |

1 | 张三 | 男 | 22 | beijing |

4 | wuhaixu | nan | 23 | hunan |

3 | 王五 | 女 | 23 | jiangsu |

+----+---------+-----+-----+---------+

4 rows in set

当然我们还可以用数字代替字段名,如下所示:整数1代表id,2代表name...

mysql> select * from user order by 4

+----+---------+-----+-----+---------+

id | name | sex | age | address |

+----+---------+-----+-----+---------+

2 | 李四 | 男 | 12 | hebei |

1 | 张三 | 男 | 22 | beijing |

3 | 王五 | 女 | 23 | jiangsu |

4 | wuhaixu | nan | 23 | hunan |

+----+---------+-----+-----+---------+

4 rows in set

mysql> select * from user order by 4,5 ;

+----+---------+-----+-----+---------+

id | name | sex | age | address |

+----+---------+-----+-----+---------+

2 | 李四 | 男 | 12 | hebei |

1 | 张三 | 男 | 22 | beijing |

4 | wuhaixu | nan | 23 | hunan |

3 | 王五 | 女 | 23 | jiangsu |

+----+---------+-----+-----+---------+

count函数的用法:

mysql> select count(*) as '总数' from user ;

+------+

总数 |

+------+

4 |

+------+

1 row in set

mysql> select count(distinct sex) as `总数` from user ;

+------+

总数 |

+------+

3 |

+------+

1 row in set

逻辑操作符:is null、between、in、like、exists、unique、all和any

mysql> select * from user where sex is null ;

Empty set

mysql> select * from user where age between 20 and 22;

+----+------+-----+-----+---------+

id | name | sex | age | address |

+----+------+-----+-----+---------+

1 | 张三 | 男 | 22 | beijing |

+----+------+-----+-----+---------+

1 row in set

mysql> select * from user where age in(22,23);

+----+---------+-----+-----+---------+

id | name | sex | age | address |

+----+---------+-----+-----+---------+

1 | 张三 | 男 | 22 | beijing |

3 | 王五 | 女 | 23 | jiangsu |

4 | wuhaixu | nan | 23 | hunan |

+----+---------+-----+-----+---------+

3 rows in set

操作符like利用通配符把一个值与类似的值进行比较,下划线代表一个数字或字符,这些符号可以复合使用。

百分号%代表零个、一个或多个字符,下划线代表一个数字或字符。这些符号可以复合使用。

mysql> select * from user where address like '%i%';

+----+------+-----+-----+---------+

id | name | sex | age | address |

+----+------+-----+-----+---------+

1 | 张三 | 男 | 22 | beijing |

2 | 李四 | 男 | 12 | hebei |

3 | 王五 | 女 | 23 | jiangsu |

+----+------+-----+-----+---------+

3 rows in set

mysql> select * from user where address like '__i%';

+----+------+-----+-----+---------+

id | name | sex | age | address |

+----+------+-----+-----+---------+

1 | 张三 | 男 | 22 | beijing |

+----+------+-----+-----+---------+

1 row in set

mysql> select * from user where exists (select id from user where age <40) ;

+----+---------+-----+-----+---------+

id | name | sex | age | address |

+----+---------+-----+-----+---------+

1 | 张三 | 男 | 22 | beijing |

2 | 李四 | 男 | 12 | hebei |

3 | 王五 | 女 | 23 | jiangsu |

4 | wuhaixu | nan | 23 | hunan |

+----+---------+-----+-----+---------+

4 rows in set

汇总函数:count、sun、max、min、avg

mysql> select count(*

) from user ;

+----------+

count(*) |

+----------+

5 |

+----------+

1 row in set

count函数配合all使用时,不包含为空的字段。address有一条为空,所以count为4

mysql> select count(all address) from user ;

+--------------------+

count(all address) |

+--------------------+

4 |

+--------------------+

1 row in set

mysql> select count(distinct address) from user ;

+-------------------------+

count(distinct address) |

+-------------------------+

4 |

+-------------------------+

1 row in set

mysql> select sum(age) from user ;

+----------+

sum(age) |

+----------+

102 |

+----------+

1 row in set

如果数据不能隐含地转化为数值类型,其结果就是0.以name为例:

mysql> select sum(name) from user ;

+-----------+

sum(name) |

+-----------+

0 |

+-----------+

1 row in set

mysql> select avg(name) from user ;

+-----------+

avg(name) |

+-----------+

0 |

+-----------+

1 row in set

mysql> select avg(age) from user ;

+----------+

avg(age) |

+----------+

20.4000 |

+----------+

1 row in set

mysql> select max(age) from user ;

+----------+

max(age) |

+----------+

23 |

+----------+

1 row in set

mysql> select min(age) from user ;

+----------+

min(age) |

+----------+

12 |

+----------+

1 row in set

数据的排序与分组:

mysql> select * from user group by age;

+----+------+-----+-----+---------+

id | name | sex | age | address |

+----+------+-----+-----+---------+

2 | 李四 | 男 | 12 | hebei |

1 | 张三 | 男 | 22 | beijing |

3 | 王五 | 女 | 23 | jiangsu |

+----+------+-----+-----+---------+

3 rows in set

mysql> select * from user group by age,address;

+----+---------+-----+-----+---------+

id | name | sex | age | address |

+----+---------+-----+-----+---------+

2 | 李四 | 男 | 12 | hebei |

5 | aa | nv | 22 | NULL |

1 | 张三 | 男 | 22 | beijing |

4 | wuhaixu | nan | 23 | hunan |

3 | 王五 | 女 | 23 | jiangsu |

+----+---------+-----+-----+---------+

5 rows in set

上面的是先根据age进行分组,再对单个分组里的address内容进行分组。

replace函数用于把某个字符或某个字符串替换为指定的一个字符(或多个字符)

mysql> select address,replace(address,'ei','ab') from user ;

+---------+----------------------------+

address | replace(address,'ei','ab') |

+---------+----------------------------+

beijing | babjing |

hebei | hebab |

jiangsu | jiangsu |

hunan | hunan |

NULL | NULL |

+---------+----------------------------+

5 rows in set

upper函数可以把字符串里的小写字母转化为大写

mysql> select upper(address) from user ;

+----------------+

upper(address) |

+----------------+

BEIJING |

HEBEI |

JIANGSU |

HUNAN |

NULL |

+----------------+

5 rows in set

lower把字符串里的大写字符转化为小写

mysql> select lower

(address) from user ;

+----------------+

lower(address) |

+----------------+

beijing |

hebei |

jiangsu |

hunan |

NULL |

+----------------+

5 rows in set

substr用来获取字符串子串的函数

mysql> select address,substr(address,1,2) from user ;

+---------+---------------------+

address | substr(address,1,2) |

+---------+---------------------+

beijing | be |

hebei | he |

jiangsu | ji |

hunan | hu |

NULL | NULL |

+---------+---------------------+

5 rows in set

选择函数,相当与oracle里的decode()

mysql> select case address when 'beijing' then '北京' else '其他' end as '地址' from user ;

+------+

地址 |

+------+

北京 |

其他 |

其他 |

其他 |

其他 |

+------+

5 rows in set

length函数用于得到字符串。数字、日期或表达式的长度,单位是字节。

mysql> select address,length(address) as '长度' from user ;

+---------+------+

address | 长度 |

+---------+------+

beijing | 7 |

hebei | 5 |

jiangsu | 7 |

hunan | 5 |

NULL | NULL |

+---------+------+

5 rows in set

lpad函数用于在字符串左侧添加字符或空格。

mysql> select address,lpad(address,30,'.') from user ;

+---------+--------------------------------+

address | lpad(address,30,'.') |

+---------+--------------------------------+

beijing | .......................beijing |

hebei | .........................hebei |

jiangsu | .......................jiangsu |

hunan | .........................hunan |

NULL | NULL |

+---------+--------------------------------+

5 rows in set

rpad函数在字符串右侧添加字符或空格

mysql> select address,rpad(address,30,'.') from user ;

+---------+--------------------------------+

address | rpad(address,30,'.') |

+---------+--------------------------------+

beijing | beijing....................... |

hebei | hebei......................... |

jiangsu | jiangsu....................... |

hunan | hunan......................... |

NULL | NULL |

+---------+--------------------------------+

5 rows in set

ASCII函数返回字符串最左侧的‘美国信息交换标准码’。

mysql> select ascii('a') ;

+------------+

ascii('a') |

+------------+

97 |

+------------+

1 row in set

mysql> select ascii('A') ;

+------------+

ascii('A') |

+------------+

65 |

+------------+

1 row in set

最常见的算数函数有:绝对值(ABS)、舍入(ROUND)、平方根(SQRT)、符号(SIGN)、幂(POWER)、上限和下限(CEIL、FLOOR)、指数(EXP)、SIN、COS、TAN

日期和时间存储的标准SQL数据类型有3种:

date:直接存储日期。date的格式是YYYY-MM-DD,范围是从0001-01-01到9999-12-31

time:直接存储时间。time的格式是HH:MI:SS.nn...,范围是从00:00:00...到23:59:61.999...

timestamp:直接存储日期和时间。timestamp的格式是YYYY-MM-DD HH:Mi:SS.nn...,范围是从0001-01-01 00:00:00...到9999-12-31 23:59:61.999...

mysql中使用now()来获取当前时间

mysql> select now() ;

+---------------------+

now() |

+---------------------+

2014-07-07 14:51:56 |

+---------------------+

1 row in set

首先创建一个简单的user表

CREATE TABLE `user` (

`id` int NOT NULL AUTO_INCREMENT ,

`name` varchar(255) NULL ,

`sex` varchar(255) NULL ,

`age` int NULL ,

`address` varchar(255) NULL ,

PRIMARY KEY (`id`)

)

然后插入数据

insert into `user`(`name`,`sex`,`age`,`address`) values('张三','男',22,'北京') ;

insert into `user`(`name`,`sex`,`age`,`address`) values('李四','男',12,'河北') ;

insert into `user`(`name`,`sex`,`age`,`address`) values('王五','女',23,'江苏') ;

insert into `user`(`name`,`sex`,`age`,`address`) values('赵六','男',21,'北京') ;

创建一张与user表关联的表

CREATE TABLE `teacher` (

`id` int NOT NULL AUTO_INCREMENT ,

`name` varchar(255) NULL ,

`stu_id` int NOT NULL ,

PRIMARY KEY (`id`),

constraint ID_FK foreign key(stu_id) references user(id)

)

insert into `teacher`(name,stu_id) values('张老师',1) ;

insert into `teacher`(name,stu_id) values('王老师',4) ;

insert into `teacher`(name,stu_id) values('吴老师',2) ;

insert into `teacher`(name,stu_id) values('方老师',2) ;

insert into `teacher`(name,stu_id) values('林老师',3) ;

等值结合:

mysql> select t.name,u.name from user u,teacher t where u.id=t.stu_id ;

+--------+---------+

name | name |

+--------+---------+

张老师 | 张三 |

吴老师 | 李四 |

方老师 | 李四 |

林老师 | 王五 |

王老师 | wuhaixu |

+--------+---------+

5 rows in set

mysql> select t.name,u.name from teacher t inner join user u on t.stu_id = u.id ;

+--------+---------+

name | name |

+--------+---------+

张老师 | 张三 |

吴老师 | 李四 |

方老师 | 李四 |

林老师 | 王五 |

王老师 | wuhaixu |

+--------+---------+

5 rows in set

在这种方式里,where子句里的结合操作符被去掉了,取而代之的是关键字inner join。要被结合的表位于join之后,而结合操作符位于关键字on之后。

上面还使用了表的别名

union操作符可以组合两个或多个select语句的结果,不包含重复的记录。如果某行的输出存在于一个查询结果里,那么其他查询结果同一行的记录就不会

再输出了。在使用union操作符时,每个select语句里必须选择同样数量的字段、同样数量的字段表达式、同样的数据类型、同样的次序--但长须不必一样。

mysql> select id from user

-> union

-> select id from teacher ;

+----+

id |

+----+

1 |

2 |

3 |

4 |

5 |

+----+

5 rows in set

union all操作符可以组合两个select语句的结果,并且包涵重复的结果。其使用规则与union一样,它与union基本上是一样的,只是一个返回重复的结果,一个不返回。

mysql> select id from user

-> union all

-> select id from teacher ;

+----+

id |

+----+

1 |

2 |

3 |

4 |

1 |

3 |

4 |

5 |

2 |

+----+

9 rows in set

索引是如何工作的呢?索引在创建之后,用于记录与杯索引字段相关联的位置值。当表里添加新数据时,索引里也会添加新项,当数据库执行查询,而且where条件里指定的字段已经设置了

索引时,数据库会首先在索引里搜索where子句里指定的值。如果在索引里找到了这个值,索引就可以返回被搜索数据在表里的实际位置。

创建单字段索引:

create index index_id on user(id) ;

创建唯一索引:

create unique index index_id on user(id) ;

组合索引是基于一个表里两个或多个字段的索引。在创建组合索引时,我们要考虑性能的问题,因为字段在索引里的次序对数据检索速度有很大的影响。一般来说,最具有限制的值应该排在前面,

从而得到最好的性能。但是,总是会在查询里指定的字段应该放在首位。

create index index_name on table_name (column1,column2) ;

隐含索引是数据库服务程序在创建对象时自动创建的。比如,数据库会为主键约束和唯一性约束自动创建索引。

什么时候选择用索引呢?唯一索引隐含地与主键共同实现主键的功能。外键经常用于与父表的结合,所以也适合设置索引。一般来说,大多数用于表结合的字段都应该设置索引。

经常在order by和group by里引用的字段也应该考虑设置索引。

何时应该避免使用索引?1、索引不应该用于小规模的表。2、当字段用于where子句做为过滤器会返回表里的大部分记录时,该字段就不适合设置索引。举例来说,图书里的索引不会

包括像the或and这样的单词。3、经常会被批量更新的表可以具有索引,但批量操作的性能会由于索引而降低。对于经常会被加载或批量操作的表来说,可以在执行批量操作之前去掉索引,

在完成操作之后再重新创建索引,这是因为当表里插入数据时,索引也会杯更新,从而增加了额外的开销。4、不应该对包涵大量null值的字段设置索引。索引对在不同记录中包涵不同数据

的字段特别有效,字段中过多的null值会严重影响索引的运行效率。5、经常被操作的字段不应该设置索引,因为对索引的维护会变得很繁重。

修改索引alter index INDEX_NAME

删除索引drop index index_name on table_name

我们应该对只包含很少不同值的字段创建索引,比如性别、州名等。

在读取大规模的表时,应该避免进行全表扫描。举例来说,当读取没有索引的表时,就会发生全表扫描,这通常会需要较长的时间才能返回数据。对于大多数大型表来说,应该考虑设置索引。

下面是应该被索引的数据;

1、做为主键的字段

2、做为外键的字段

3、在结合表里经常使用的字段

4、经常在查询里做为条件的字段

5、大部分是唯一值的字段

在调整sql语句里还有其他一些性能考虑:

1、使用like操作符和通配符

2、避免or操作符

3、避免having子句。

4、避免大规模排序操作

5、使用存储过程

6、在批加载时关闭索引

1、使用like操作符

mysql> select address from user where address like '%jing' ;

+---------+

address |

+---------+

beijing |

+---------+

1 row in set

mysql> select address from user where address like '___

jing' ;

+---------+

address |

+---------+

beijing |

+---------+

1 row in set