首先创建一个简单的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...，范围是从0...到23:59:61.999... timestamp：直接存储日期和时间。timestamp的格式是YYYY-MM-DD HH:Mi:SS.nn...，范围是从0001-01-01 0...到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
查看更多关于mysql命令详解 - mysql数据库栏目 - 自学php的详细内容...