目录

数据库03 /库、表、记录的详细操作、单表查询

昨日回顾

.1客户端完整连接语句

mysql -h 127.0.0.1 -P 3306 -u root -p 密码;

.2创建表

create table 表名(    字段1 类型(宽度) 约束条件,    字段2 类型(宽度) 约束条件,    # 添加外键    foreign key(字段2) references 表2(表2的某个字段))

.3数据类型:

数值类型(整型\浮点型)  字符串 时间日期类型 枚举和集合 无符号:字段1 类型(宽度) unsigned,

.4完整性约束

not null 非空default 默认值  default 1unique 唯一primary key 主键 不为空且唯一auto_increment 自增 foreign key 外键

1.外键的补充

1.删除或修改被关联字段

book表和publish表为多对一关系，book表的pid字段外键关联到了publish表的id字段1.查看外键关系名称    show create table book;#    | book  | CREATE TABLE `book` (#    `id` int(11) NOT NULL AUTO_INCREMENT,#    `name` char(10) DEFAULT NULL,#    `pid` int(11) DEFAULT NULL,#     PRIMARY KEY (`id`),#     KEY `pid` (`pid`),     CONSTRAINT `book_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `publish` (`id`)#     ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |2.删除外键关系    alter table book drop foreign key book_ibfk_1（外键名称）#   删除之后，看表的结构还是有，但是已经删除了3.创建表完成之后，添加外键    alter table book add foreign key(pid) references publish(id);4.创建外键时指定外键名称#    1.create table t1(#        id int,#        pid int,#        constraint fk_t1_publish foreign key(pid) references publish(id)#    );#    2.创建表完成之后,后添加外键关系#   alter table book add constraint fk_t1_publish foreign key(pid) references #   publish(id);#    3.创建外键时指定外键名称（创建后不能修改外键名称，只能删除后再添加）5.查看所有外键的信息#    select                                                         REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,table_name,CONSTRAINT_NAME from information_schema.key_column_usage;

2.级联

级联的三种模式（级联中有一种模式是级联模式）外键约束的三种约束方式（都是针对父表的约束）有foreign key的表就是子表（关联表）;对应的另一个表是父表（被关联表）模式一： district：严格约束(默认的),父表不能删除或者更新已经被子表数据引用的记录模式二：cascade：级联模式：父表的操作，对应的子表关联的数据也跟着操作 。    constraint fk_t1_publish foreign key(pid) references publish(id) on delete     cascade on update cascade;模式三：set null：置空模式，父表操作之后，子表对应的数据（外键字段）也跟着被置空。    （被关联字段删除时，关联它的数据会置成null）    通常的一个合理的约束模式是：删除的时候子表置空；更新的时候子表级联。指定模式的语法：foreign key(外键字段)references 父表(主键字段)on delete 模式 on update 模式;注意：删除置空的前提条件是 外键字段允许为空，不然外键会创建失败。外键虽然很强大，能够进行各种约束，但是外键的约束降低了数据的可控性和可拓展性。通常在实际开发时，很少使用外键来约束。

2.库的详细操作

2.1创建数据库

1.1 语法    create database 数据库名 charset utf8;#1.2 数据库命名规则#    可以由字母、数字、下划线、＠、＃、＄#    区分大小写 #表名不区分#    唯一性#    不能使用关键字如 create select#    不能单独使用数字#    最长128位    # 基本上跟python或者js的命名规则一样

2.2数据库的相关操作

1 查看数据库    show databases;    show create database db1;    select database();  #查询当前所使用的数据库2 选择数据库    USE 数据库名3 删除数据库    DROP DATABASE 数据库名;4 修改数据库    alter database db1 charset utf8;

3.表的详细操作

3.1创建表 create

#语法：create table 表名(字段名1 类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3 类型[(宽度) 约束条件]);#注意：#1. 在同一张表中，字段名是不能相同#2. 宽度和约束条件可选、非必须，宽度指的就是字段长度约束(指的是在内存中开辟空间的大小)，例如：char(10)里面的10#3. 字段名和类型是必须的

3.2查看表 show

mysql> describe t1; #查看表结构，可简写为：desc 表名mysql> show create table t1\G; #查看表详细结构，可加\G（分行去显示）

3.3修改表 alter

1. 修改表名    alter table 表名 rename 新表名;2. 增加字段    alter table 表名 add 字段名  数据类型 [完整性约束条件…];    #注意这里可以通过逗号来分割，一下添加多个约束条件    alter table 表名 add 字段名  数据类型 [完整性约束条件…];    alter table 表名 add 字段名  数据类型 [完整性约束条件…]  first;    #添加这个字段的时候，把它放到第一个字段位置去。    alter table 表名 add 字段名  数据类型 [完整性约束条件…]  after 字名;    #after是放到后的这个字段的后面去了，我们通过一个first和一个after就可以将新添加的字段放到表的任意字段位置了。                        3. 删除字段    alter table 表名 drop 字段名;4. 修改字段    alter table 表名 modify 字段名 数据类型 [完整性约束条件…];    alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];      #change比modify还多了个改名字的功能，这一句是只改了一个字段名    alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件…];    #这一句除了改了字段名，还改了数据类型、完整性约束等等的内容    alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件…] after id；5.建完表之后给字段添加外键属性#    给一个字段添加外键属性的语句：alter table 表2名 add foreign key(表2的一个字段) references 表1名(表1的一个字段);注意一点：在mysql里面表名是不区分大小写的，如果你将一个名为t1的(小写的t1)改名为一个T1（大写的T1），是完全没用的，因为在数据库里面表名都是小写的。

4.行(记录)的详细操作

4.1介绍

#MySQL数据操作： DML#在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括#    使用INSERT实现数据的插入#    UPDATE实现数据的更新#    使用DELETE实现数据的删除#    使用SELECT查询数据

4.2记录的增加（插入）insert

1. 插入完整数据（顺序插入）    语法一：    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);     #指定字段来插入数据，插入的值要和你前面的字段相匹配    语法二：    INSERT INTO 表名 VALUES (值1,值2,值3…值n);     #不指定字段的话，就按照默认的几个字段来插入数据2. 指定字段插入数据    语法：    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);3. 插入多条记录    语法：#插入多条记录用逗号来分隔    INSERT INTO 表名 VALUES        (值1,值2,值3…值n),        (值1,值2,值3…值n),        (值1,值2,值3…值n);  4. 插入查询结果    语法：    INSERT INTO 表名(字段1,字段2,字段3…字段n)                     SELECT (字段1,字段2,字段3…字段n) FROM 表2                    WHERE …;#将从表2里面查询出来的结果来插入到我们的表中，但是注意查询出来的数据要和我们前面指定的字段要对应好5.插入一条数据    INSERT INTO 表名 VALUE (值1,值2,值3…值n);     #只能插入一条数据

4.3记录的修改（更新）update

语法：    UPDATE 表名 SET         字段1=值1,  #注意语法，可以同时来修改多个值，用逗号分隔        字段2=值2,        WHERE CONDITION; #更改哪些数据，通过where条件来定位到符合条件的数据

4.4记录的删除 delete

语法：    DELETE FROM 表名         WHERE CONITION; #删除符合条件的一些记录 delete from t3;  删除所有的数据,但是不会重置自增字段的数据号 delete from t3 where id = 1;删除指定的数据,删除id字段数据为1的那一行记录 清空表 truncate 表名;  自增字段会重置

4.5记录的查询 select

之前我们写的查询语句是这样的：select id,name from employee;实际上我们在select每个字段的时候，省略了一个表名，有的人可能会这样写，select employee.id,employee.name from employee;

5.单表查询

5.1关键字执行的优先级

1.from2.where3.group by4.having5.select6.distinct7.order by8.limit#1.找到表:from#2.拿着where指定的约束条件，去文件/表中取出一条条记录#3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组#4.将分组的结果进行having过滤#5.执行select#6.去重distinct#7.将结果按条件排序：order by#8.限制结果的显示条数#四则运算:#   SELECT salary*12 FROM employee;#自定义显示格式 concat用法#   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary FROM employee;

5.2where约束

1. 比较运算符：> < >= <= <> != (<>相当于!=)    SELECT name FROM employee WHERE post='sale'; 2. between 10 and 15 值在10到15之间    mysql> select * from employee where id between 10 and 153. in(80,90,100)  值是80或90或100    select * from employee where id in(1,3,6)  等价于id=1 or id=3 or id=6;4. like 'egon%'    pattern可以是%或_， %表示任意多字符_表示一个字符    select * from employee where name like "wu%";    select * from employee where name like "al_";结果空    select * from employee where name like "al__";#结果ale    mysql> select * from employee where name like "al___"; 三个下划线的无法匹配到    alex,结果为空5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not    select * from employee where id>10 and name like "al%";    select * from employee where not id>10;id小于等于10的,not取反

5.3分组查询 group by

5.3.1分组，分组的原因

分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等1.select * from employee group by post;    * 分组后默认拿的是每组的第一个的数据记录2.ONLY_FULL_GROUP_BY(严格模式)    ONLY_FULL_GROUP_BY：限制必须是分组依据，或集合函数得到的统计结果    (非分组依据，非统计结果对于分组就没有意义)3.分组时可以跟多个条件,那么这个多个条件同时重复才算是一组,group by 后面多条件用逗号分隔    select post,max(salary) from employee group by post,id;4.#如果我们用设置了unique约束的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义  #多条记录之间的某个字段值相同，该字段通常用来作为分组的依据5.GROUP BY关键字和GROUP_CONCAT()函数一起使用,比如说我想按部门分组，每个组有哪些员工，都显示出来，怎么搞    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;    #按照岗位分组，并查看组内所有成员名，通过逗号拼接在一起    SELECT post,GROUP_CONCAT(name,':',salary) as emp_members FROM employee GROUP BY post;

5.4聚合函数

1.聚合函数聚合的是组的内容，若是没有分组，则默认一组    where后面不能写聚合函数2.SELECT COUNT(*) FROM employee;  #count是统计个数用的  SELECT COUNT(*) FROM employee WHERE depart_id=1;  #后面跟where条件的意思是统计一下满足depart_id=1这个的所有记录的个数3.SELECT MAX(salary) FROM employee;  #max（）统计分组后每组的最大值，这里没有写group by，那么就是统计整个表中所有记录中薪资最大的，薪资的值4.SELECT MIN(salary) FROM employee;5.SELECT AVG(salary) FROM employee;6.SELECT SUM(salary) FROM employee;  SELECT SUM(salary) FROM employee WHERE depart_id=3;7.group_concat:分组后select查询的字段只能是分组依据，想要获取组内的其他相关信息，需要借助此函数

5.5having 分组再过滤

1.having过滤后面的条件可以使用聚合函数    select post ,max（salary） from employee group by post having max(salary）>20000;                                                                2.having的语法格式和where是一模一样的，只不过having是在分组之后进行的进一步的过滤，where不能使用聚合函数，having是可以使用聚合函数的    #！！！执行优先级从高到低：where > group by > having     #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。    #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,having是可以使用聚合函数                                                                3.having只能在group by后面运行

5.6distinct去重

1.select distinct post from employee;    注意：select的字段必须写在distinct后面2.select distinct post,id from employee;这句话,意思就是post和id两个组合在一起同时重复的才算是重复数据3.distinct查询出某个字段不重复的记录的条数：select count(distinct post) from employee;

5.7order by 排序

1.升序    select  *  from employee order by age;    select  *  from employee order by age asc;2.降序 desc    select  *  from employee order by age desc;3.多条件排序    按照age字段升序,age相同的数据,按照salary降序排列    select  *  from employee order by age asc,salary desc;

5.8limit 限制查询的记录数

1.#取出工资最高的前三位    SELECT * FROM employee ORDER BY salary DESC     LIMIT 3;#默认初始位置为0,从第一条开始顺序取出三条     2.SELECT * FROM employee ORDER BY salary DESC  LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条 3.SELECT * FROM employee ORDER BY salary DESC       LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条4.分页显示，每页显示5条（注意：第二数字5指的是每次显示5条记录）    mysql> select * from  employee limit 0,5;    mysql> select * from  employee limit 5,5;    mysql> select * from  employee limit 10,5;

5.9模糊匹配（使用正则表达式查询）

前面我们用like做模糊匹配，只有%和_，局限性比较强，所以我们说一个正则，之前我们是不是学过正则匹配，你之前学的正则表达式都可以用，正则是通用的SELECT * FROM employee WHERE name REGEXP '^ale';SELECT * FROM employee WHERE name REGEXP 'on$';SELECT * FROM employee WHERE name REGEXP 'm{2}';