MySQL

首页 > 代码库 > MySQL

2024-08-31 01:43:32 223人阅读

一。基本SQL语句

SQL:(Structured Query Language) 结构化查询语言专门用来和关系型数据库进行通信的语言

SQL语句中的大小写问题：
1）关键字、函数名、列名和索引名不区分大小写
2）数据库名、表名、别名及视图名区分大小写的(因为linux区分大小写的)
3）存储过程、事件不区分大小写，但是触发器区分大小写

SQL语句功能：
增删改查

SQL语句的分类：
DML：Data Manipulation Language 数据操纵语言
insert
update
delete
DCL：Data Control Language 数据控制语言
grant
revoke
DDL：Data Definition Language 数据定义语言
create
alter
drop
truncate
DQL：Data Query Language 数据查询语言
select

基本SQL语句：
1、查看当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
2、对数据库的操作
1）查看当前操作的数据库
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
2）选择操作哪个数据库
mysql> use test;
3）查看系统中有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | 信息模式数据库，保存服务器的信息
| mysql 核心管理数据库，保存用户和权限信息，记录了和系统安全相关的信息
| performance_schema 性能模式数据库，搜索服务器的性能参数
| test | 测试数据库
+--------------------+
4）查看当前数据库下有哪些表
mysql> show tables;
5）创建数据库
mysql> create database updb1;
注意：数据库名不能重复
6）删除数据库(轻易不要这么做，风险大)
mysql> drop database updb1;
3、对表的操作 (表一定是存在数据库中的)
1）创建表结构
语法：create table 表名 (字段1名字段类型,字段2名字段类型,... ...,字段n名字段类型);
mysql> create table t1 (id int,name char(10));
2）查看表结构
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
字段名类型是否允许为空键值默认值其他
3）查看建表时使用的语句
mysql> show create table t1;
4）向表中插入数据
（1）插入时不指定字段，在写值的时候的顺序就需要与表中的字段顺序一一对应
语法：insert into 表名 values (值1,值2,...);
mysql> insert into t1 values(1,"Tom");
（2）插入时指定字段
语法：insert into 表名(字段名,......) values (值1,... ...);
mysql> insert into t1(id) values(2);
mysql> insert into t1(name) values("Mary");
mysql> select * from t1; //查询表中数据
+------+------+
| id | name |
+------+------+
| 1 | Tom |
| 2 | NULL |
| NULL | Mary |
+------+------+
3 rows in set (0.02 sec)
（3）向表中插入多条数据
mysql> insert into t1 values (4,"Jack"),(6,"Kite"),(8,"LiLei");
5）查询表中数据
mysql> select * from t1; //全表查询
mysql> select id from t1; //指定字段查询
mysql> select name,id from t1; //同时查询多个字段
6）修改表结构 alter
a、增加字段
（1）向表中增加字段(默认添加到最后一个字段)
语法：alter table 表名 add 字段名字段类型 [first|after 字段名];
mysql> alter table t1 add sex char(10);
（2）将字段添加为表中的第一个字段
mysql> alter table t1 add qq int first;
（3）在某个字段后面插入字段
mysql> alter table t1 add tel int(15) after id;
b、修改字段属性
change：既能修改字段类型，又能修改字段名字
modify：只能修改字段类型
修改字段类型
语法：alter table 表名 modify 字段名字段类型;
mysql> alter table t1 modify name char(15);
修改字段名字
语法：alter table 表名 change 原字段名新字段名字段类型;
mysql> alter table t1 change qq msn char(15);
注意：如果只修改字段类型，那么原字段名和新字段名一样
如果只修改字段名字，那么字段类型与原来的字段的类型保持一致。
c、删除字段
语法：alter table 表名 drop 字段名;
mysql> alter table t1 drop msn;
7）修改表名
语法：alter table 表名 rename 新表名;
mysql> alter table t1 rename test1;
8）更新表中的数据
语法：update 表名 set 字段名=值[,字段名2=值2] where 条件;
mysql> update test1 set id=3 where name="Mary";
mysql> update test1 set tel=45629009,sex="male" where name="Tom";
9）删除表中记录
（1）删除表中指定行
mysql> delete from test1 where id>=4;
（2）删除表中全部记录
mysql> delete from test1;
10）截断表 truncate
truncate table 表名;
delete：可以回滚的
truncate：不可以回滚的，删除数据同时会释放空间。大表数据清除一定要用truncate，速度快。
11）删除表
drop table 表名;

二。MySQL的函数

一、时间函数
1、查看当前时间
mysql> select curtime();
mysql> select current_time();
2、查看当前日期
mysql> select curdate();
mysql> select current_date();
3、查看当前系统时间
mysql> select now();
mysql> select sysdate();

二、聚合函数
求和 sum(字段名)
求平均数 avg(字段名)
求最大值 max(字段名)
求最小值 min(字段名)
统计匹配行数 count(字段名) count(*)：一般用于统计表中有多少条非空记录。

准备环境
mysql> select database();
+------------+
| database() |
+------------+
| up1 |
+------------+
mysql> create table score (sno int,sname char(10),ule int,ula int,uoa int);
mysql> insert into score values (1,"tom",70,80,90),(2,"mary",55,75,65),(3,"jack",75,45,95),(4,"mike",90,100,86);

mysql> select max(ule),min(uoa) from score;
+----------+----------+
| max(ule) | min(uoa) |
+----------+----------+
| 90 | 65 |
+----------+----------+
1 row in set (0.02 sec)

mysql> select max(ule) mae,min(uoa) mio from score; //给列起别名
+------+------+
| mae | mio |
+------+------+
| 90 | 65 |
+------+------+
1 row in set (0.00 sec)

列的别名：
列名 [as] 别名
统计表中的记录数
mysql> select count(*) from score;
计算ula的平均成绩
mysql> select avg(ula) from score;
查询各科的总成绩
mysql> select sum(ula),sum(ule),sum(uoa) from score;
查询每个学生的姓名及总成绩
mysql> select sname,ule+ula+uoa from score;
mysql> select sname,ule+ula+uoa total from score;

-----------------------------------------------------------------------------------------------------------------
插曲：参考手册853页case语句
求一行中的最大值
mysql> select sname,case when (ule>ula) and (ule>uoa) then ule when (ula>ule) and (ula>uoa) then ula else uoa end max_score from score;
+-------+-----------+
| sname | max_score |
+-------+-----------+
| tom | 90 |
| mary | 75 |
| jack | 95 |
| mike | 100 |
+-------+-----------+
4 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------------------------
三、其它函数
1、连接函数 concat() concatenate
mysql> select concat(sno,sname) from score;
mysql> select concat(sno,‘ ‘,sname) from score;
2、转换成大写 upper()
转换成小写lower()
mysql> select upper(sname) from score;
mysql> update score set sname="Tom" where sname="tom";
mysql> select lower(sname) from score;
3、求长度 length()
mysql> select sname,length(sname) from score;

三。MySQL查询语句

一、指定条件查询
select 用来过滤字段
from 从哪些表中查询数据
where 用来过滤记录(行)
查询ula及格的人的姓名和ula成绩
mysql> select sname,ula from score where ula>=60;
查询每一科都及格的人的姓名
mysql> select sname from score where ule>=60 and ula>=60 and uoa>=60;
查询有任何一科不及格的学生的全部信息
mysql> select * from score where ule<60 or ula<60 or uoa<60;
二、模糊查询
like
使用通配符：
_：匹配单个字符
%：匹配0个或多个字符
mysql> select sname from score where sname like "___";
mysql> select * from score where sname like ‘m%‘; //以m开头的人
mysql> select * from score where sname like ‘%m‘; //以m结尾的人
mysql> select * from score where sname like ‘%m%‘; //名字含有m的人
mysql> show variables like ‘da%‘; //查看变量值
+-----------------+-------------------+
| Variable_name | Value |
+-----------------+-------------------+
| datadir | /data/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
三、分组 group by 字段名
mysql> alter table score add class int;
mysql> insert into score values (5,"rose",75,65,95,2);
mysql> update score set class="1" where sno=1;
mysql> update score set class="2" where sno=2;
mysql> update score set class="1" where sno=3;
mysql> update score set class="2" where sno=4;
mysql> select class,sum(ula),avg(ula) from score group by class;
//求每个班的总成绩和平均成绩

注意：group by里面条件不能使用where，可以与having连用
显示班级人数超过2人的班级的总成绩及平均成绩
mysql> select class,sum(ula),avg(ula) from score group by class having count(*)>2;

四、排序
order by 字段名 [asc|desc]
asc：按照升序排序，是默认的
desc：按照降序排序
mysql> select sname,ula from score order by ula desc;
+-------+------+
| sname | ula |
+-------+------+
| mike | 100 |
| Tom | 80 |
| mary | 75 |
| rose | 65 |
| jack | 45 |
+-------+------+
mysql> select * from score order by uoa,ula;

mysql> select sname,ula from score order by 2 desc; //按照字段的序号排序

五、限制输出 limit
limit n：只显示前n条记录
limit m,n：从第m+1行开始显示n行
mysql> select sname,ula from score order by ula desc limit 3;
mysql> select sname,ula from score order by ula desc limit 2,2; //显示第3和第4行

mysql> select sname,ula from score order by ula desc limit 1;
mysql> select sname,ula from score order by ula desc limit 0,1;

六、子查询
也叫嵌套查询，将一个查询的查询结果作为外层查询的条件。
查询ula成绩最高的人的学号和姓名
mysql> select sno,sname from score where ula=(select max(ula) from score);

小结：
1、select关键字的顺序
-----------------------------------------------------------------------------------------------
关键字功能是否必须
-----------------------------------------------------------------------------------------------
select 过滤要返回的列或表达式是
-----------------------------------------------------------------------------------------------
from 要检索的表仅当需要从表中检索数据时使用
-----------------------------------------------------------------------------------------------
where 过滤行否
-----------------------------------------------------------------------------------------------
group by 分组仅在按组计算聚集时使用
-----------------------------------------------------------------------------------------------
having 组级过滤(与group by连用) 否
-----------------------------------------------------------------------------------------------
order by 对输出排序，默认是升序否
-----------------------------------------------------------------------------------------------
limit 限制输出的行否
-----------------------------------------------------------------------------------------------
2、SQL 语句的执行顺序如下:
from where group by having select order by limit

查：select
慢查询。
查询的方式：
1、单表查询
基本查询
使用函数查询
2、多表查询
子查询
联结查询
语法：select 列 from 表 [查询条件]；
（1）基本查询
mysql> select * from q1; 全表查询
mysql> select name,sex from q1; 查询几列

mysql> select * from q1 where id=1; 带条件的查询
mysql> select name,sex from q1 where id=1;

带字符串的要用 "" 包起来：
mysql> select id,name from q1 where sex="W";
mysql> select id,name from q1 where sex=W;
ERROR 1054 (42S22): Unknown column ‘W‘ in ‘where clause‘

创建一个表，练习用的：一个商店，记录的供货商的信息。
id name price city street riqi
int varchar float varchar varchar date
mysql> create table shop (id int,name varchar(20),price float(5,2),city varchar(20),street varchar(20),riqi date);

mysql> insert into shop values(1001,".1 niu1",5.99,"shenyang","sanhaojie","2015-08-02");
mysql> insert into shop values(1002,".2 niu2",6.99,"shenyang","sanhaojie","2015-07-02");
mysql> insert into shop values(1003,".3 niu3",9.99,"shenyang","sanhaojie","2015-07-15");
mysql> insert into shop values(1004,"4 ma4",5.39,"beijing","zhongguancun","2015-06-15");
mysql> insert into shop values(1005,"5 ma5",10.90,"beijing","zhongguancun","2015-06-16");
mysql> insert into shop values(1006,"6 ma6",20.00,"beijing","zhongguancun","2015-06-20");
mysql> insert into shop values(1007,"wangqi",10.50,"dalian","xianlu","2015-05-20");
mysql> insert into shop values(1008,"houba",100.00,"dalian","xianlu","2015-05-30");
mysql> insert into shop values(1009,"zhaojiu",999.00,"dalian","xianlu","2015-05-01");

（3）限制查询结果的输出 limit
mysql> select * from shop limit 0,2;
+------+---------+-------+----------+-----------+------------+
| id | name | price | city | street | riqi |
+------+---------+-------+----------+-----------+------------+
| 1001 | .1 niu1 | 5.99 | shenyang | sanhaojie | 2015-08-02 |
| 1002 | .2 niu2 | 6.99 | shenyang | sanhaojie | 2015-07-02 |
第1个数，从哪行开始
第2个数，向下查询几行
（4）对输出结果进行排序 order by
mysql> select * from shop order by price; 升序
mysql> select * from shop order by price desc; 降序

mysql> select * from shop order by street;

mysql> select * from shop order by street,price;
mysql> select * from shop order by street,price desc;

mysql> select * from shop order by price,id;
说明：指定两列排序的时候，只有第一列出现重复值的时候，第2列才起作用。

（5）where 组合其他的条件
1）比较运算符 > < = != <> >= <= between ... and ...
mysql> select * from shop where price > 10.00;

mysql> select * from shop where city="beijing";

mysql> select * from shop where city>"beijing"; 尽量别用

上货的日期限定是 5月份：
riqi >= 2015-05-01 and riqi <= 2015-05-31

mysql> select * from shop where riqi >= "2015-05-01" and riqi <= "2015-05-31";
mysql> select * from shop where riqi between "2015-05-01" and "2015-05-31";

2）逻辑运算符 and or !
mysql> select * from shop where price > 10.00 or price < 6.00;

mysql> select * from shop where !(price > 10.00 or price < 6.00);

说明：and的优先级高于 or ；先or 后and，需要用 and ( or )

3）in ；not in 匹配多个值

mysql> select * from shop where id in (1003,1005,1007);
mysql> select * from shop where id not in (1003,1005,1007);
mysql> select * from shop where id in (1003,1005,1007,1010);

4）is NULL ； is not NULL 匹配空值
mysql> select * from shop where name is NULL;
mysql> select * from shop where name is not NULL;

5）白板 ""

6）like 模糊查询
通配符：
_ 任意的单个字符
% 字符出现任意次
mysql> select * from shop where name like "_____";
mysql> select * from shop where price like "_____";

mysql> select * from shop where price like "_%";
mysql> select * from shop where price like "10%";
mysql> select * from shop where price like "10.%";
mysql> select * from shop where price like "%9";
注意:
1、模糊查询比较消耗时间，不要过度使用；
2、用的时候，尽量不要让通配符出现在开始的位置，避免全表扫描。
7）regexp 正则表达式
元字符：. * + ? {} ^ $
[[:<:]] 词首符 [[:>:]] 词尾符
转义符号 \\
mysql> select * from shop where name regexp "^\\.";
mysql> select * from shop where name regexp "[[:<:]]w";
mysql> select * from shop where name regexp "[0-9][[:>:]]";

select * from shop where id like "100";
没有返回值
select * from shop where id regexp "100";
所有的数据
说明：like匹配的是整个列的数据，当没有加通配符的时候，条件要求的值是什么，列的值就得是什么；正则表达式是使用的基本字符的匹配方式；
----------------------------------------------------------------------
简单的算数运算：
mysql> select 2+1; 支持 + - * / % 运算
+-----+
| 2+1 |
+-----+
| 3 |
-------------------------------------------
新的需求：
（1）把城市和街道连接到一起：
输出： shenyang shi sanhaojie
（2）如果有其他的支出，如何显示成本：

（3）输出结果是小写的，转变成大写：

解决：
1、字符串函数
（1）连接函数 concat()
mysql> select concat(city," shi ",street) as address from shop where city is not null;

mysql> select concat(city," shi ",street) as address from shop where city="shenyang";

关键字：as 指定别名；现在使用的是输出列的别名。

（2）
mysql> select name,price+1 as price from shop where price is not null;

（3）
upper() 小写转大写
lower() 大写转小写
mysql> select upper(name) from shop;

其他的字符串函数，自己看书<<MySQL入门很简单>>。

2、数据汇总函数：
说明：
这些函数都忽略空值 NULL
sum 和 avg 需要数值型数据
（1）count() 统计
mysql> select count(*) from shop;
+----------+
| count(*) |
+----------+
| 10 |
mysql> select count(name) from shop;
+-------------+
| count(name) |
+-------------+
| 9 |

（2）max() 求最大值
（3）min() 求最小值
mysql> select max(price) from shop;
mysql> select min(price) from shop;
mysql> select min(name) from shop;

（4）sum() 求和

（5）avg() 求平均数
mysql> select sum(price) from shop;
+------------+
| sum(price) |
+------------+
| 1168.76 |

mysql> select avg(price) from shop;
+------------+
| avg(price) |
+------------+
| 129.862222 |

3、时间和日期函数
date() 返回时间和日期数据的日期部分
time() 返回时间和日期数据的时间部分
year() 返回时间和日期数据的年部分；
month() 返回时间和日期数据的月部分；
应用：
统计哪一年的数据
mysql> select sum(price) from shop where year(riqi)=2015;
+------------+
| sum(price) |
+------------+
| 1168.76 |
统计某个月的数据
mysql> select sum(price) from shop where month(riqi)=6;
+------------+
| sum(price) |
+------------+
| 36.29 |

day()
hour()
minute()
second()

4、其他的函数
查看当前所在的数据库
mysql> select database();
+------------+
| database() |
+------------+
| db1 |

查看当前的连接用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2015-07-31 10:53:44 |

mysql> select version();
+------------+
| version() |
+------------+
| 5.5.11-log |
-------------------------------------------
分组查询：
需求：每个城市价格最贵的商品信息？
使用分组查询解决。
group by
不忽略空值，所有的空值都是1组。

-------------------------------------
mysql> select * from t1;
+------+------+------+
| id | name | sex |
+------+------+------+
| 101 | niu1 | M |
| 102 | niu2 | W |
| 103 | niu3 | W |
| 104 | niu4 | M |
| 105 | niu5 | W |

group by 的连接函数 group_concat()：

mysql> select sex,group_concat(name) from t1 group by sex;
+------+--------------------+
| sex | group_concat(name) |
+------+--------------------+
| W | niu2,niu3,niu5 |
| M | niu1,niu4 |
按照性别分组，把每种性别的名字列表显示。
算男的几个人，女的几个人？
mysql> select sex,count(sex) from t1 group by sex;
+------+------------+
| sex | count(sex) |
+------+------------+
| W | 3 |
| M | 2 |

----------------------------
mysql> select city,max(price) as city_max_price from shop where city is not null group by city having city_max_price>=20 order by city_max_price desc limit 0,1;
单表查询的关键字就用上了。

关键字的书写顺序：
select --> from --> where --> group by --> having --> order by --> limit

关键字的执行顺序：
from --> where --> group by --> having --> select --> order by --> limit

关键字功能是不是必须的
select 查询是
from 从表中检索数据只有从表中取数据时是必须的
where 行级过滤否；需要过滤输出行的条件指定
group by 分组否
having 分组过滤否
order by 排序否
limit 限制输出行否

子查询：
嵌套查询。把内层查询结果作为外层查询的判断条件。
select xx from table where (select )
连接用的字符：
比较运算符：= > < >= <= !=
关键字：IN NOT IN ANY ALL
找出价格最贵的人名：
mysql> select name from shop where price=(select max(price) from shop);
+---------+
| name |
+---------+
| zhaojiu |

mysql> select price from shop where price >=100;
+--------+
| price |
+--------+
| 100.00 |
| 999.00 |

mysql> select name from shop where price=(select price from shop where price >=100);
ERROR 1242 (21000): Subquery returns more than 1 row
如果内层的查询结果是 >= 2个的时候，不能使用 = 连接。
mysql> select name from shop where price IN (select price from shop where price >=100);
+---------+
| name |
+---------+
| houba |
| zhaojiu |
mysql> select name from shop where price NOT IN (select price from shop where price >=100);
使用IN或NOT IN

find -perm
ANY +；只要满足查询结果的任意一个，就可以使用。
ALL -；只有满足所有的查询结果，才可以使用。

mysql> select price from shop where price >= 20;
+--------+
| price |
+--------+
| 20.00 |
| 100.00 |
| 999.00 |

mysql> select name from shop where price >= ALL (select price from shop where price >= 20);
+---------+
| name |
+---------+
| zhaojiu |
--------------------------
用到一个关键字：distinct 去重
--------------------------
建立子查询的思路：
逐渐进行；先建立内层查询，再建立外层查询，如果都没有问题，在把内层查询嵌套进外层查询。

四。索引与表的联结

索引：
1、什么是索引？
索引是一个创建在表上的特殊文件，对表中的一列或几列值进行排序的一种结构，记录表中的数据指针。

2、索引的功能？
加快数据的查询速度。
索引在MySQL中，也叫做“键”，是存储引擎用于快速找到行记录的一种数据结构。
索引的类型：
B树索引：MyISAM InnoDB 存储引擎使用。
hash索引：Memory 存储引擎使用。

3、什么时候使用索引？
在查询大表的数据时候使用。

4、什么时候不用索引？
（1）索引使数据的增删改操作变慢，每修改一次数据，索引也要更新一次；
（2）查询索引也要花费时间，因此小表没有必要创建索引；
（3）保存索引也要耗费存储空间，空间不足的时候就别建了。

5、如何使用索引？
（1）在哪些情况下无法使用索引：
where 里面出现不等于
where 里面出现函数
使用模糊查询 like 把通配符放在开始的位置 %a
如果表中列上出现很多重复的数据，索引的效果也不好
（2）创建索引的注意事项：
如果表的数据频繁的发生变化，就不要创建索引了；
尽量在经常被查询的列上创建索引；
尽量在数据比较整齐的列上创建索引；比如说 int

开始实验：
1、创建索引：
（1）创建普通索引

创建表的同时创建索引（基本没用）：
mysql> create table t2 (id int,name varchar(21),index(id));
在表的 id 列上创建索引；关键字是 index=key
mysql> show create table t2;
KEY `id` (`id`)

mysql> create table t3 (id int,name char(11),key id_key (id));
mysql> show create table t3;
KEY `id_key` (`id`)

是表已经创建好了，并且有数据，这时候创建索引：
mysql> create index id_key on shop(id); 给表shop的id列创建索引 id_key
mysql> show create table shop;
KEY `id_key` (`id`)

mysql> desc shop;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |

mysql> alter table t1 add index id_index(id);给t1表的id列创建索引id_index
mysql> show create table t1;
KEY `id_index` (`id`)

删除：
mysql> drop index id on t2;
mysql> alter table t3 drop index id_key;

测试：普通索引是不是会加快查询的速度？
mysql> set profiling=1; 开启统计sql语句的执行时间
mysql> select * from shop;
mysql> select id from shop;
mysql> select * from shop where id=1001;
mysql> select id from shop where id=1003;
mysql> select id from shop where price > 10.00;

mysql> show profiles;
+----------+------------+------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------+
| 1 | 0.02058500 | select * from shop |
| 2 | 0.00027675 | select id from shop |
| 3 | 0.00195025 | select * from shop where id=1001 |
| 4 | 0.00030750 | select id from shop where id=1003 |
| 5 | 0.00033350 | select id from shop where price > 10.00 |

mysql> set profiling=0;

（2）创建唯一性索引
保证列中不可以出现重复的值，也叫唯一性约束。
关键字：unique
mysql> create table t4 (id int unique,name char(11));
mysql> show create table t4;
UNIQUE KEY `id` (`id`)

mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |

mysql> insert into t4 values(1,"a1");

mysql> insert into t4 values(1,"a2");
ERROR 1062 (23000): Duplicate entry ‘1‘ for key ‘id‘

mysql> insert into t4(name) values("a2");
mysql> insert into t4(name) values("a3");
mysql> select * from t4;
+------+------+
| id | name |
+------+------+
| 1 | a1 |
| NULL | a2 |
| NULL | a3 |
缺欠：唯一性约束对空值无效。

解决缺欠：加非空约束
关键字：NOT NULL
mysql> create table t5 (id int not null,name char(11) not null);

mysql> insert into t5 (id) values(1),(2);
mysql> insert into t5 (name) values("a1"),("a2");

mysql> select * from t5;
+----+------+
| id | name |
+----+------+
| 1 | |
| 2 | |
| 0 | a1 |
| 0 | a2 |
在非空约束里面，字符型数据变成白板，数值型数据变成0。
mysql> select * from t5 where name="";
+----+------+
| id | name |
+----+------+
| 1 | |
| 2 | |

把非空约束和唯一性约束组合到一起：
唯一 + 非空 = 主键 primary key
mysql> create table t6 (id int unique not null,name char(11) unique not null);

mysql> desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(11) | NO | UNI | NULL | |
一个表只能有一个主键。
mysql> show create table t6;
UNIQUE KEY `id` (`id`),
UNIQUE KEY `name` (`name`)

mysql> insert into t6(id) values(1),(2);
ERROR 1062 (23000): Duplicate entry ‘‘ for key ‘name‘

mysql> insert into t6(name) values("1");
mysql> select * from t6;
+----+------+
| id | name |
+----+------+
| 0 | 1 |
mysql> insert into t6(name) values("2");
ERROR 1062 (23000): Duplicate entry ‘0‘ for key ‘id‘

给主键创建的索引叫做主索引。

主键：唯一标识表中的记录的。
外键：需要依赖另一张表的主键的数据。
主键和外键是关系表的粘合剂。
创建主键的时候，没有要求。

当需要同时创建主键和外键的时候，要求：
（1）存储引擎是 innodb的；
（2）创建主键的列和创建外键的列的数据类型必须是一致的；int >>> int

mysql> show engines; 查看默认的存储引擎。
| InnoDB | DEFAULT

创建主键表：
表1 学员的个人信息表：
mysql> create table stu1 (id int primary key,name varchar(20) not null,tel char(11)not null);

mysql> desc stu1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| tel | char(11) | NO | | NULL | |

创建外键表：
表2：学员成绩表
mysql> create table stu2 (id int primary key,ule tinyint,ula tinyint,oracle tinyint,foreign key (id) references stu1(id));
foreign key (id) 给id列创建外键
references stu1(id) 外键依赖 stu1 表的id列
mysql> show create table stu2;
KEY `id` (`id`),
CONSTRAINT `stu2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `stu1` (`id`)

向表插入数据：
mysql> insert into stu2 values(2015060801,60,65,75);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`stu2`, CONSTRAINT `stu2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `stu1` (`id`))
主键还没有数据，外键是不允许插入数据的。
mysql> insert into stu1 values(2015060801,"yangchao","13112345678");
mysql> insert into stu1 values(2015060802,"cuidongyang","13212345678");
mysql> insert into stu1 values(2015060803,"wangxu","13312345678");

mysql> insert into stu2 values(2015060801,65,75,85);
mysql> insert into stu2 values(2015060802,66,76,86);

在建表之后补充创建主键：
mysql> alter table stu2 add primary key (id);

删除外键：
mysql> alter table stu2 drop foreign key stu2_ibfk_1;
stu2 表名
stu2_ibfk_1 外键名
再添加外键：建表之后再创建外键。
mysql> alter table stu2 add foreign key(id) references stu1(id);
foreign key(id) 给 stu2 的id 列创建外键

mysql> delete from stu1 where id=2015060801;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`stu2`, CONSTRAINT `stu2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `stu1` (`id`))
mysql> delete from stu1 where id=2015060803;
说明：id=2015060801行数据有外键表依赖，不可以删除；
mysql> update stu1 set id=2015060810 where id=2015060801;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`stu2`, CONSTRAINT `stu2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `stu1` (`id`))
说明：id=2015060801行数据有外键表依赖，不可以修改；

在外键表上创建：
级联删除 on delete cascade
级联修改 on update cascade
mysql> alter table stu2 add foreign key (id) references stu1(id) on update cascade on delete cascade;
show create table stu2\G;设置级联修改时先把子表原来的外键删除以免产生影响
mysql> delete from stu1 where id=2015060801;
mysql> select * from stu1;
+------------+-------------+-------------+
| id | name | tel |
+------------+-------------+-------------+
| 2015060802 | cuidongyang | 13212345678 |
mysql> select * from stu2;
+------------+------+------+--------+
| id | ule | ula | oracle |
+------------+------+------+--------+
| 2015060802 | 66 | 76 | 86 |

mysql> update stu1 set id=2015060810 where id=2015060802;
mysql> select * from stu1;
+------------+-------------+-------------+
| id | name | tel |
+------------+-------------+-------------+
| 2015060810 | cuidongyang | 13212345678 |
mysql> select * from stu2;
+------------+------+------+--------+
| id | ule | ula | oracle |
+------------+------+------+--------+
| 2015060810 | 66 | 76 | 86 |

=================================================
关系表查询：
stu1 stu2 使用主键和外键关联起来了。

mysql> create table stu3 (id int,work enum("Y","N"),sal int);
mysql> insert into stu3 values(2015060801,"Y",5000);
mysql> insert into stu3 values(2015060802,"Y",7000);
mysql> insert into stu3 values(2015060803,"Y",6500);
mysql> insert into stu3 values(2015060810,"N",0);

mysql> select * from stu1;
+------------+-------------+-------------+
| id | name | tel |
+------------+-------------+-------------+
| 2015060801 | yangchao | 13112345678 |
| 2015060802 | cuidongyang | 13212345678 |
| 2015060803 | wangxu | 13312345678 |
| 2015060810 | cuidongyang | 13212345678 |
| 2015080605 | a1 | 13512346578 |

mysql> select * from stu2;
+------------+------+------+--------+
| id | ule | ula | oracle |
+------------+------+------+--------+
| 2015060801 | 65 | 75 | 85 |
| 2015060802 | 66 | 76 | 86 |
| 2015060803 | 66 | 76 | 86 |
| 2015060810 | 66 | 76 | 86 |

mysql> select * from stu3;
+------------+------+------+
| id | work | sal |
+------------+------+------+
| 2015060801 | Y | 5000 |
| 2015060802 | Y | 7000 |
| 2015060803 | Y | 6500 |
| 2015060810 | N | 0 |

表的联结查询：
1、2个表的联结：
输出内容：输出每个学员的学号，名字，ULE的成绩？

mysql> select id,name,ule from stu1,stu2;
ERROR 1052 (23000): Column ‘id‘ in field list is ambiguous
当2个表中出现名字是一样的列名的时候，查询时要带上表名。

mysql> select stu1.id,name,ule from stu1,stu2;
出结果了，但是结果不够友好。
这个查询没有指定查询条件，因此结果出现笛卡尔积。

mysql> select stu1.id,name,ule from stu1,stu2 where stu1.id=stu2.id;
+------------+-------------+------+
| id | name | ule |
+------------+-------------+------+
| 2015060801 | yangchao | 65 |
| 2015060802 | cuidongyang | 66 |
| 2015060803 | wangxu | 66 |
| 2015060810 | cuidongyang | 66 |
这是对的。
mysql> select stu1.id,name,sal from stu1,stu3 where stu1.id=stu3.id;
+------------+-------------+------+
| id | name | sal |
+------------+-------------+------+
| 2015060801 | yangchao | 5000 |
| 2015060802 | cuidongyang | 7000 |
| 2015060803 | wangxu | 6500 |
| 2015060810 | cuidongyang | 0 |

mysql> select stu2.id,ula,sal from stu2,stu3 where stu2.id=stu3.id;
+------------+------+------+
| id | ula | sal |
+------------+------+------+
| 2015060801 | 75 | 5000 |
| 2015060802 | 76 | 7000 |
| 2015060803 | 76 | 6500 |
| 2015060810 | 76 | 0 |

这种查询方式叫做内部联结。还有另外一种写法：
mysql> select stu1.id,name,ule
-> from stu1 inner join stu2
-> on stu1.id=stu2.id;

外部联结：
（1）左联结
mysql> select stu1.id,name,ule from stu1 left outer join stu2 on stu1.id=stu2.id;
+------------+-------------+------+
| id | name | ule |
+------------+-------------+------+
| 2015060801 | yangchao | 65 |
| 2015060802 | cuidongyang | 66 |
| 2015060803 | wangxu | 66 |
| 2015060810 | cuidongyang | 66 |
| 2015080605 | a1 | NULL |

（2）右联结
mysql> select stu1.id,name,ule from stu1 right outer join stu2 on stu1.id=stu2.id;
+------------+-------------+------+
| id | name | ule |
+------------+-------------+------+
| 2015060801 | yangchao | 65 |
| 2015060802 | cuidongyang | 66 |
| 2015060803 | wangxu | 66 |
| 2015060810 | cuidongyang | 66 |

3个表联结：
输出：id name ula sal
mysql> select stu1.id,name,ula,sal from
-> (
-> ( stu1 inner join stu2 on stu1.id=stu2.id )
-> inner join stu3 on stu1.id=stu3.id
-> );
+------------+-------------+------+------+
| id | name | ula | sal |
+------------+-------------+------+------+
| 2015060801 | yangchao | 75 | 5000 |
| 2015060802 | cuidongyang | 76 | 7000 |
| 2015060803 | wangxu | 76 | 6500 |
| 2015060810 | cuidongyang | 76 | 0 |

==================================================

自增：auto_increment
id号自动增加。
qq 年龄
员工编号
使用要求：
（1）列的数据类型是数值型，整型
（2）列有主键或唯一性约束

mysql> create table t7 (id int unique auto_increment,name char(11));

mysql> insert into t7 (name) values("a1"),("a2");
mysql> select * from t7;
+----+------+
| id | name |
+----+------+
| 1 | a1 |
| 2 | a2 |
默认是从 1 开始自增。

mysql> insert into t7 values(10,"a10");
mysql> insert into t7 (name) values("niua"),("niub");
mysql> select * from t7;
+----+------+
| id | name |
+----+------+
| 1 | a1 |
| 2 | a2 |
| 10 | a10 |
| 11 | niua |
| 12 | niub |
如果中间出现断档，从最大值开始自增。
mysql> delete from t7;
mysql> insert into t7 (name) values("b1"),("b2");
mysql> select * from t7;
+----+------+
| id | name |
+----+------+
| 13 | b1 |
| 14 | b2 |
使用 delete 删掉数据之后，会保存记录，依然从最大值开始自增。

mysql> truncate t7; 清空表中的所有操作，不支持回滚
mysql> insert into t7 (name) values("b1"),("b2");
mysql> select * from t7;
+----+------+
| id | name |
+----+------+
| 1 | b1 |
| 2 | b2 |
使用 truncate 清空表的数据之后，可以让表的数据从 1 开始自增。
=====================================================================
默认值：
注册，表格性。
* 必填
在数据库中，出现一些定值。
mysql> create table t8 (id int,name char(11) default "unknown",sal int default 10000);
mysql> desc t8;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(11) | YES | | unknown | |
| sal | int(11) | YES | | 10000 | |

mysql> insert into t8(id) values(1);

mysql> select * from t8;
+------+---------+-------+
| id | name | sal |
+------+---------+-------+
| 1 | unknown | 10000 |
============================================

MySQL

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > MySQL

MySQL

看完仍有疑问？有类似问题直接问程序猿