本文共 14129 字，大约阅读时间需要 47 分钟。
建表的格式
create table 表名( #这条表示把表建在当前库下，库名.表名 表示把表建到指定库中 字段名 类型（宽度） 约束条件， 字段名 类型（宽度） 约束条件 );当前库下创建表
mysql> create table top1 (id int(3),name char(10)); Query OK, 0 rows affected (0.02 sec)指定库创建表
mysql> create table h2.top (id int(3),name char(10)); Query OK, 0 rows affected (0.02 sec)删除表
mysql> drop table top6;查看建表语句
mysql> show create table top\G; *************************** 1. row *************************** Table: top Create Table: CREATE TABLEtop ( id int(3) DEFAULT NULL, name char(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 查看表结构
mysql> desc top1; ±------±---------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±------±---------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | name | char(10) | YES | | NULL | |向表中插入内容
insert 语句 1 插入一条数据 写法1 mysql> insert into top1(id,name) values (1,“test”); 注：推荐写法 表中有多少字段都写上 写法2 mysql> insert into top1 values (2,“test1”); 注：不写表中的字段 但值 一定要对应上批量添加多条数据
mysql> insert into top1 values -> (2,22,“boy”,“tt”), -> (3,23,“boy”,“tt1”), -> (4,24,“boy”,“tt2”); mysql> select * from top1; ±-----±-----±-----±--------+ | id | age | sex | newname | ±-----±-----±-----±--------+ | 1 | 21 | boy | tony | | 2 | 22 | boy | tt | | 3 | 23 | boy | tt1 | | 4 | 24 | boy | tt2 |查看表中内容
select语句 1 查看全部数据 mysql> select * from top1; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 1 | 21 | boy | tony | | 2 | 22 | boy | tt | | 3 | 23 | boy | tt1 | | 4 | 24 | boy | tt2 | 注：在表中的数据很大的时候 千万不要用*查寻 会很慢2 查看指定字段
mysql> select id,name from top1; ±-----±-----+ | id | name | ±-----±-----+ | 1 | tony | | 2 | tt | | 3 | tt1 | | 4 | tt2 | 注：想要查寻哪个字段的就直接写字段名，多个字段用,分隔3 查看指定范围内数据 关键字 limit
查看前两行数据 mysql> select * from top1 limit 2; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 1 | 21 | boy | tony | | 2 | 22 | boy | tt |查看指定偏移量数据
mysql> select * from top1 limit 1,2; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 2 | 22 | boy | tt | | 3 | 23 | boy | tt1 | 注：该条命令的意思是查寻第一行下的两行数据，第一行不显示哦（不包含第一行）查看指定行的数据
mysql> select * from top1 limit 2,1; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 3 | 23 | boy | tt1 | 注：第2行下的1行也就是第3行，要查看指定行就按照此算法 5,1 就是第6行查看最后一行数据
mysql> select * from top1 order by id desc limit 1; 注：利用了倒序排列4 按指定条件查找 关键字 where
mysql> select * from top1 where id=3; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 3 | 23 | boy | tt1 | ±-----±-----±-----±-----+ mysql> select * from top1 where name=“tt”; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 2 | 22 | boy | tt | ±-----±-----±-----±-----+ 显示top1表中name字段中是空的内容 mysql> select * from top1 where name is null;5 条件查寻 关键字 where and or
and 并关系 两个条件必须全满足 mysql> select * from top1 where id=1 and name=“tony”; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 1 | 21 | boy | tony | mysql> select * from top1 where id=1 and name=“tony” and age=22; Empty set (0.00 sec) 注：只要有一个条件不满足就查寻不到范围查寻
mysql> select * from top1 where id>1 and id <5; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 2 | 22 | boy | tt | | 3 | 23 | boy | tt1 | | 4 | 24 | boy | tt2 |or 或关系 只要满足一个即可
mysql> select * from top1 where id=1 or name=“tt1” or age=22; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 1 | 21 | boy | tony | | 2 | 22 | boy | tt | | 3 | 23 | boy | tt1 |6 查寻排序 关键词 order by desc倒序查寻 asc正序排列 默认为asc
格式： select 指定查寻内容 from 表 order by 指定表结构中的查寻条件 排序方法 按id列查寻 mysql> select * from top1 order by id desc; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 4 | 24 | boy | tt2 | | 3 | 23 | boy | tt1 | | 2 | 22 | boy | tt | | 1 | 21 | boy | tony | 按name列查寻 mysql> select * from top1 order by name; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 5 | 30 | girl | abc | | 6 | 55 | boy | elk | | 7 | 18 | girl | ha | | 1 | 21 | boy | tony | | 2 | 22 | boy | tt | | 3 | 23 | boy | tt1 | | 4 | 24 | boy | tt2 |7 联表查寻（方法很多 本人水平有限 只能写出简单的语句）
关于多表连接语法规则 1.首先找涉及到的所有表 2.找到表和表之间的关联列 3.关联条件写在on后面 A join B on 关联列 4. 所有需要查询的信息放在select后 5. 其他的过滤条件where group by having order by limit 往最后放 6.注意：对多表连接中，驱动表选择数据行少的表。后续所有表的关联列尽量是主键或唯一键（表设计），至少建立一个索引。比较查寻
方法1 全部数据联表 mysql> select * from top1,top2 where top1.id=top2.id; ±-----±-----±-----±-----±-----±-----±-----±--------+ | id | age | sex | name | id | age | sex | newname | ±-----±-----±-----±-----±-----±-----±-----±--------+ | 1 | 21 | boy | tony | 1 | NULL | NULL | | | 2 | 22 | boy | tt | 2 | NULL | NULL | | | 3 | 23 | boy | tt1 | 3 | 30 | boy | toney | 方法2 指定列联表 语法:书写时必须是表明.列 mysql> select top1.id, top2.newname from top1,top2 where top1.id=top2.id; select country.name,country.surfacearea,city.name,city.population from city join country on city.countrycode=country.code where city.population < 100; ±-----±--------+ | id | newname | ±-----±--------+ | 1 | | | 2 | | | 3 | toney | 方法3 inner jone 是比较运算符，只返回符合条件的行 mysql> select * from top1 inner join top2 on top1.id=top2.id; ±-----±-----±-----±-----±-----±-----±-----±--------+ | id | age | sex | name | id | age | sex | newname | ±-----±-----±-----±-----±-----±-----±-----±--------+ | 1 | 21 | boy | tony | 1 | NULL | NULL | | | 2 | 22 | boy | tt | 2 | NULL | NULL | | | 3 | 23 | boy | tt1 | 3 | 30 | boy | toney |左连接 left join 包含left join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL)
mysql> select * from top1 left join top2 on top1.id=top2.id; ±-----±-----±-----±-----±-----±-----±-----±--------+ | id | age | sex | name | id | age | sex | newname | ±-----±-----±-----±-----±-----±-----±-----±--------+ | 1 | 21 | boy | tony | 1 | NULL | NULL | | | 2 | 22 | boy | tt | 2 | NULL | NULL | | | 3 | 23 | boy | tt1 | 3 | 30 | boy | toney | | 4 | 24 | boy | tt2 | NULL | NULL | NULL | NULL | | 5 | 30 | girl | abc | NULL | NULL | NULL | NULL | | 6 | 55 | boy | elk | NULL | NULL | NULL | NULL | | 7 | 18 | girl | ha | NULL | NULL | NULL | NULL |右连接 right join 包含right join右表所有行，如果右表中某行在左表没有匹配，则结果中对应行左表的部分全部为空(NULL)
ysql> select * from top1 right join top2 on top1.id=top2.id; ±-----±-----±-----±-----±-----±-----±-----±--------+ | id | age | sex | name | id | age | sex | newname | ±-----±-----±-----±-----±-----±-----±-----±--------+ | 1 | 21 | boy | tony | 1 | NULL | NULL | | | 2 | 22 | boy | tt | 2 | NULL | NULL | | | 3 | 23 | boy | tt1 | 3 | 30 | boy | toney修改表结构
增删改表中字段（列） 格式 alter table 表名 修改动作; 修改动作： 1 add 添加新字段 例： 查看现有表结构 mysql> desc top1; ±------±---------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±------±---------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | name | char(10) | YES | | NULL | |添加一条新字段
mysql> alter table top1 add age int(2); mysql> desc top1; ±------±---------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±------±---------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | name | char(10) | YES | | NULL | | | age | int(2) | YES | | NULL |添加多条字段 并指定添加位置
mysql> alter table top1 -> add class char(20) first, -> add sex enum(“boy”,“girl”), -> add love set(“book”,“play”,“move”) after name; Query OK, 0 rows affected (0.09 sec)mysql> desc top1;
±------±--------------------------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±------±--------------------------±-----±----±--------±------+ | class | char(20) | YES | | NULL | | | id | int(3) | YES | | NULL | | | name | char(10) | YES | | NULL | | | love | set(‘book’,‘play’,‘move’) | YES | | NULL | | | age | int(2) | YES | | NULL | | | sex | enum(‘boy’,‘girl’) | YES | | NULL | |注：添加多字段用,分隔 after指定新加字段的位置 在某条字段下面 first 把新加字段放在首位
enum和set 为枚举类型 后面接收数据类型时会提到2 drop 删除已有字段（列）
删除单条字段 mysql> alter table top1 drop love; mysql> desc top1; ±------±---------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±------±---------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | name | char(10) | YES | | NULL | | | age | int(2) | YES | | NULL | |删除多条字段
mysql> alter table top1 drop class,drop sex; 注：删除每一个字段都要加一个drop然后用,分隔3 modify 修改已有字段类型
格式 alter table 表名 modify 字段名 新类型（宽度） 约束条件;查看现有字段类型
mysql> desc top1; ±------±-------------------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±------±-------------------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | age | int(2) | YES | | NULL | | | sex | enum(‘boy’,‘girl’) | YES | | NULL | | | name | char(5) | NO | | NULL | |错误做法
mysql> alter table top1 modify name varchar(10); ysql> desc top1; ±------±-------------------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±------±-------------------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | age | int(2) | YES | | NULL | | | sex | enum(‘boy’,‘girl’) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | 这里我们可以看到 原来表中Null是不许为空的 修改后变成了可以为空正确方法
mysql> alter table top1 modify name char(8) not null; mysql> desc top1; ±------±-------------------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±------±-------------------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | age | int(2) | YES | | NULL | | | sex | enum(‘boy’,‘girl’) | YES | | NULL | | | name | char(8) | NO | | NULL | | 注：修改已有字段类型 不需要变的 按当时建表语句抄下来 如 not null 否则都会变成默认值4 change 修改字段名
格式 alter table 表名 change 原字段名 新字段名 类型（宽度） 限制条件 查看现有表 mysql> desc top1; ±------±-------------------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±------±-------------------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | age | int(2) | YES | | NULL | | | sex | enum(‘boy’,‘girl’) | YES | | NULL | | | name | char(8) | NO | | NULL | |mysql> alter table top1 change name newname varchar(10);
mysql> desc top1; ±--------±-------------------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±--------±-------------------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | age | int(2) | YES | | NULL | | | sex | enum(‘boy’,‘girl’) | YES | | NULL | | | newname | varchar(10) | YES | | NULL | | 注：change也可以修改字段类型 使用方法跟modify一样 原表名和新表名一样的情况下跟modify一样吧 我测试的是这样5 rename 修改表名
格式 alter table 原表名 rename 新表名 查看表名 mysql> show tables; ±-------------+ | Tables_in_h2 | ±-------------+ | top | | top1 |mysql> alter table top1 rename newtop;
mysql> show tables; ±-------------+ | Tables_in_h2 | ±-------------+ | newtop | | top |复制表
注：表结构中key列的值不会被复制给新表 1 复制表中所有内容 格式 create table 新表 select * from 原表查看原库中的表
mysql> show tables; ±-------------+ | Tables_in_h2 | ±-------------+ | top | | top1 |复制表
mysql> create table top2 select * from top1;查看复制结果
mysql> show tables; ±-------------+ | Tables_in_h2 | ±-------------+ | top | | top1 | | top2 |查看新表中内容
mysql> select * from top1; ±-----±-----±-----±--------+ | id | age | sex | newname | ±-----±-----±-----±--------+ | 1 | NULL | NULL | | | 2 | NULL | NULL | | | 3 | 30 | boy | toney |2 复制表中指定数据
格式 create table 新表 select 要复制原表中的字段 from 原表查看原表中结构
mysql> select * from top1; ±-----±-----±-----±--------+ | id | age | sex | newname | ±-----±-----±-----±--------+ | 1 | NULL | NULL | | | 2 | NULL | NULL | | | 3 | 30 | boy | toney |复制表中指定数据
mysql> create table top3 select id,newname from top1;查看复制结果
mysql> show tables; ±-------------+ | Tables_in_h2 | ±-------------+ | top | | top1 | | top2 | | top3 |查看新表中内容
mysql> select * from top3; ±-----±--------+ | id | newname | ±-----±--------+ | 1 | | | 2 | | | 3 | toney |3 只复制表结构
复制表 mysql> create table top4 select * from top1; 删除表中所有数据 mysql> delete from top4; 查看新表 mysql> select * from top4; Empty set (0.00 sec)mysql> desc top4;
±--------±-------------------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±--------±-------------------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | age | int(2) | YES | | NULL | | | sex | enum(‘boy’,‘girl’) | YES | | NULL | | | newname | char(10) | YES | | NULL | |更新数据
update 语法格式：update 表名 set 字段=新值，如果多个…where 条件 （一定要注意加where条件） mysql> select * from top1; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 1 | 25 | boy | tony | | 2 | 22 | boy | tt | | 3 | 23 | boy | tt1 | 如果不加where 的结果 mysql> update top1 set age=25; mysql> select * from top1; ±-----±-----±-----±-----+ | id | age | sex | name | ±-----±-----±-----±-----+ | 1 | 25 | boy | tony | | 2 | 25 | boy | tt | | 3 | 25 | boy | tt1 | | 4 | 25 | boy | tt2 | | 5 | 25 | girl | abc | | 6 | 25 | boy | elk | | 7 | 25 | girl | ha | 字段的值都改变了 后果很严重删除表中数据（行）
按指定条件删除 mysql> delete from top1 where id=7; 删除表中所有数据 mysql> delete from top1; 查看表中数据 mysql> select * from top1; Empty set (0.00 sec) 查看表结构 mysql> desc top1; ±------±-------------------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±------±-------------------±-----±----±--------±------+ | id | int(3) | YES | | NULL | | | age | int(2) | YES | | NULL | | | sex | enum(‘boy’,‘girl’) | YES | | NULL | | | name | char(5) | YES | | NULL | | 所以delete只删除表中数据不删除表结构删除表中数据的另一种方法
mysql> truncate table top6; 同delete一样也只是删除表中数据不删除表结构修改表名
格式 rename table 原表名 to 新表名 mysql> rename table top5 to top6; 同mysql> alter table top6 rename top5; 结果一样建表时为字段加条件
mysql> create table top6( -> id int(3)primary key auto_increment, -> name char(10) not null, -> age int(3) unsigned, -> tel int(11) default 0 -> ); primary key auto_increment 主键自增 not null 不允许为空 unsigned 无符号范围（不允许为负数）默认范围为有符号范围 default 默认值 zerofill 用0补足位宽，默认是在数字前以空格补全mysql> insert into top6(name,age) values(“test”,20);
mysql> insert into top6(name,age) values(“tony”,30); mysql> select * from top6; ±—±-----±-----±-----+ | id | name | age | tel | ±—±-----±-----±-----+ | 1 | test | 20 | 0 | | 2 | tony | 30 | 0 |union all 和 union
作用: 多个结果集合并查询的功能需求: 查询中或者美国的城市信息
SELECT * FROM city WHERE countrycode=‘CHN’ OR countrycode=‘USA’;改写为:
SELECT * FROM city WHERE countrycode=‘CHN’ UNION ALL SELECT * FROM city WHERE countrycode=‘USA’;使用union all的效率要比上面的效率高,走的索引规则不同.
面试题: union 和 union all 的区别 ?
union all 不做去重复 (效率高) union 会做去重操作转载地址：http://vkrpb.baihongyu.com/