1、概述
【1】使用率最高
2、数据库的重要性
【1】数组、集合、文件 【2】数据管理系统-MySQL 【3】持久化数据，容易管理、查询
3、相关概念
【1】DB database 数据库 【2】DBMS 数据库管理系统（MySQL就是其中一种） 【3】SQL 结构查询语言 【4】所有的DBMS都支持SQL 【5】简单易学 【6】可以进行非常复杂和高级的数据库操作
4、数据库的特点
【1】一个数据库可有多个表，每个表有一个名字，用来作为唯一性标识 【2】表由列组成，类似Java中的属性
5、MySQL概述
【1】MySQL现在属于甲骨文Oracle公司 【2】体积小、易使用 【3】社区版、企业版 【4】卸载与安装 【5】配置文件概述
9、服务的启动与停止
【1】service mysqld restart #重启 【2】service mysqld start #启动 【3】service mysqld stop #停止
10、MySQL服务端的登录与退出
【1】mysql -hlocalhost -uroot -p 【2】mysql -uroot -p
12、常见命令
【1】show databases; 【2】use mydb 【3】show tables; 【4】select database(); // 查看当前所在库 【5】create table stu_info( id int, name varchar(20)); 【6】desc stu_info; // 查看表的属性 【7】select * from stu_info; //查询 【8】insert into stu_info (id,name) values (1,'陈翔'); 【9】select version(); // 查看版本
15、语法规范概述
【1】不区分大小写，建议关键字大写，表名、列名小写 【2】每条命令最好用分好结尾 【3】每条命令根据需要，可以进行缩进或换行 【4】注释 /* */ #
16、图形用户化界面
【1】Navicat
18、四张表概述
【1】DQL：Data Query Language 【2】DML：Data Martipulation Language 【3】DDL：Data Define Language 【4】TCL：Transaction Control Language 【5】员工表 CREATE TABLE employees( employee_id int(6) PRIMARY KEY, first_name varchar(20), last_name varchar(25), email varchar(25), phone_number varchar(20), job_id varchar(10), salary double(10,2), commission_pct double(4,2), manager_id int(4), department_id int(4), hiredate datetime); 为什么部门用数字，因为汉字是字符串，耗费空间，用编号对应就行了。节省空间、减少了数据冗余 【6】部门表 CREATE TABLE departments( department_id int(4) PRIMARY KEY, department_name varchar(3), manager_id int(6), location_id int(4)); 【7】位置表 CREATE TABLE locations( location_id int(11) PRIMARY KEY, street_address varchar(40), postal_code varchar(12), city varchar(30), state_province varchar(25), country_id varchar(2)); 【8】工种表 CREATE TABLE jobs( job_id varchar(10) PRIMARY KEY, job_title varchar(35), min_salary int(6), max_salary int(6));
19、基础查询概述
【1】语法 SELECT 查询列表 FROM 表名; 特点： ·查询列表—>可以是表中的字段、常量值、表达式、函数 ·查询的结果是一个虚拟的表格 【2】查询表中的单个字段 SELECT last_name FROM employees; 【3】查询表中多个字段 SELECT last_name,salary,email FROM employees; 【4】查询表中所有字段 SELECT * FROM employees;
21、可以查询的值列举
# 使用那个库 USE study; # 方式一 SELECT * FROM employees; # 查询常量值 SELECT 100; SELECT '起飞'; # 查询表达式 SELECT 100%98; # 查询函数 SELECT VERSION();
23、起别名
【1】便于理解 # 起别名 SELECT 100%98 AS result; 【2】如果查询的字段有重名的问题，可以区分开 【3】SELECT 100%98 result; AS可以省略 【4】SELECT salary AS 'out_put' FROM employees; 【5】去重 # 查询部门ID SELECT DISTINCT department_id FROM employees;
25、+号的作用
【1】+号就是做加法的，没有连接的作用 【2】把姓和名拼接起来 SELECT GROUP_CONCAT(first_name,last_name) AS 姓名 FROM employees WHERE employee_id=1; 【3】显示表结构 DESC departments; 【4】IFNULL的使用 SELECT IFNULL( commission_pct, 0 ) AS 奖金率, commission_pct FROM employees;
28、条件查询
【1】WHERE后面就是条件 SELECT first_name AS 姓 FROM employees WHERE employee_id = 1; 【2】条件表达式 > < = != <> >= <= 【3】逻辑运算符 && || ！ and or not 【4】模糊查询 like bettween and in is null 【5】查询工资>12000 SELECT * FROM employees WHERE salary > 12000; 【6】查询部门编号不等于1的员工名和部门编号 SELECT last_name, department_id FROM employees WHERE department_id != 1; ---------------------------------------------- SELECT last_name, department_id FROM employees WHERE department_id <> 1; 【7】逻辑运算符 and or not 与或非 【8】工资在1-2万之间的 SELECT last_name, salary, commission_pct FROM employees WHERE salary >= 10000 AND salary <= 20000; 【9】查询部门编号不是在90到110之间的,或者工资高于15000的员工信息 SELECT * FROM employees WHERE department_id > 1 OR salary > 15000; 【10】模糊查询 like bettwen and in is null is not null 【11】查询姓包含l的姓列表 SELECT first_name FROM employees WHERE first_name LIKE '%l%'; 【12】like 一般和通配符（如%%）配合使用。 【13】查询第二个字符为l，第四个字符为c的姓 SELECT first_name, salary FROM employees WHERE first_name LIKE '_l_c%'; 【14】查询员工名中第二个字符为_的员工名 SELECT first_name, salary FROM employees WHERE first_name LIKE '_\_%'; ------------------------------------------- SELECT first_name, salary FROM employees WHERE first_name LIKE '_$_%' ESCAPE '$';
32、bettwen and关键字
【1】提供语句的简洁度 SELECT * FROM employees WHERE employee_id BETWEEN 2 AND 3; 【2】包含临界值 【3】两个值不可以颠倒顺序，否则查出的东西就为空了
33、模糊查询in关键字
【1】批量查询 SELECT * FROM employees WHERE employee_id in (1,3); 【2】判断某字段的值是否属于列表中的某一项 【3】提高了语句简洁度 【4】值的类型要求一致或兼容
34、is null 使用
【1】查询字段为空的结果 SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL; 【2】查询字段不为空的结果 SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL;
35、安全等于
【1】<=> SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL; 【2】安全等于也可以用于普通相等 SELECT last_name FROM employees WHERE salary <=> 15000; 【3】可读性略差
36、查询条件
SELECT last_name, department_id, salary * 12 *( 1+IFNULL ( commission_pct, 0 )) AS 年薪 FROM employees; SELECT salary, last_name FROM employees WHERE commission_pct IS NULL AND salary < 20000;
39、数据库的排序查询
【1】从低到高 SELECT * FROM employees ORDER BY salary ASC; 【2】从高到低 SELECT * FROM employees ORDER BY salary DESC; 【3】两个都不写 默认是升序 SELECT * FROM employees ORDER BY salary;
40、排序查询示例
【1】条件+排序查询 SELECT * FROM employees WHERE department_id >= 2 ORDER BY hiredate; 【2】查询姓名与年薪 SELECT last_name, salary * 12 *( 1+IFNULL ( commission_pct, 0 )) AS 年薪 FROM employees ORDER BY salary; 【3】按照姓名的函数排序 SELECT LENGTH( last_name ), last_name, salary FROM employees ORDER BY LENGTH( last_name ); 【4】先按照工资，再按照员工编号排序 SELECT LENGTH( last_name ), last_name, salary, department_id FROM employees ORDER BY salary ASC, employee_id DESC; 【5】order by 支持单个字段、多个字段、表达式等 【6】order by 是在查询条件的后面，limit子句除外
43、常见函数概述
【1】优点：隐藏实现细节、提高复用性
【2】类型：字符函数、数学函数、日期函数、其他函数、流程控制函数
【3】length函数
SELECT LENGTH('陈翔六点半之铁头无敌');
【4】concat
SELECT CONCAT(last_name,'_',first_name) as 完整姓名 from employees;
【5】UPPER LOWER
SELECT UPPER('tom'),LOWER('TOM');
【6】substr substring
select SUBSTR('陈翔六点半',3) as result;
select SUBSTR('陈翔六点半',1,2) as result;
【7】trim
select length(trim(' aaaaaa ')) as result;
【8】lapd、rpad
select lpad('陈翔',10,'*') as result;
select lpad('陈翔六点半',2,'*') as result;
46、数学函数
【1】round 四舍五入 select round (1.45); 【2】ceil 向上取整 select ceil (1.21); 【3】floor 向下取整 select floor (9.99999); 【4】truncate select truncate(1.678932,1); 【5】mod 取余 select mod(10,3);
47、日期函数
【1】now() 返回当前时间 【2】curdate() 【3】年份 select year(now()) year; 【4】date_format 将日期转换成字符 select DATE_FORMAT(now(),'%y年%m月%d月') as result;
48、其他函数
select user(); select database(); select version();
49、流程控制函数
【1】if语句 select last_name, if(salary>12000,'工资大于1.2','工资小于1.2') FROM employees; 【2】根据部门不同加工资 select last_name, salary as old_salary, department_id, case department_id when 1 then salary*1.1 when 2 then salary*1.2 else salary end as new_salary from employees; 【3】多重if select last_name, salary as old_salary, case when salary>18000 then 'A级工资' when salary>12000 then 'B级工资' else 'C级工资' end as salary_level from employees;
50、常见函数总结
【1】length concat substr instr trim upper lower lpad rpad replace 【2】round ceil floor truncate mod 【3】now curdate curtime year mouth monthname day hour minute second str_to_date date_fromat 【4】version database user 【5】控制函数 if case
53、分组函数
【1】分组函数、统计、聚合 【2】sum求和 avg平均值 max最大值 min最小值 count计算个数 【3】各用法的示例 select sum(salary) from employees; ------------------------ select avg(salary) from employees; ------------------------- select max(salary) from employees; ------------------------- select min(salary) from employees; ------------------------- select count(salary) from employees; 【4】参数支持哪些类型 sum avg 数值型、忽略null max min count 可以处理任何类型、忽略null 【5】以上分组函数都忽略null值 【6】可以和distinct搭配使用实现去重 select sum(DISTINCT salary), sum(salary) from employees; 【7】统计总数 select count(*) from employees;
58、题目练习
【1】查询工资的各个维度 select max(salary) as max_salary, min(salary) as min_salary, avg(salary) as avg_salary, sum(salary) as sum_salary from employees; 【2】查询部门为1的员工个数 select count(*) from employees where department_id = 1;
59、分组查询
【1】每个工种的最高工资 select max(salary), job_id from employees group by job_id; 【2】查询每个位置的部门个数 select count(*), location_id from departments GROUP BY location_id;
61、分组查询
【1】分组查询并添加条件 select avg(salary), department_id from employees where email like '%4%' GROUP BY department_id; 【2】查询有奖金的每个领导手下员工的最高工资 select max(salary), manager_id from employees where commission_pct is not null group by manager_id;
62、复杂查询条件与分组查询
【1】查询每个部门的员工个数 select count(*), department_id from employees GROUP BY department_id; 【2】根据1的结果筛选，看哪个部门的员工个数>2 select count(*), department_id from employees GROUP BY department_id HAVING count(*) >2; 【3】查询每个工种最高工资的，然后查询最高工资>12000 select max(salary), job_id from employees where commission_pct is not null GROUP BY job_id HAVING max(salary) > 12000; 【4】查询领导编号>111,这个领导手下员工的最低工资 select min(salary), manager_id from employees where manager_id > 111 group by manager_id 【5】根据4 查询最低工资大于10000 select min(salary), manager_id from employees where manager_id > 111 group by manager_id having min(salary)>10000 【6】条件怎么添加，要看是分组前还是分组后的字段，能用分组前筛选优先考虑分组前筛选
64、按函数分组
【1】按员工姓名长度，分组查询员工个数，筛选员工个数>1 select count(*), length(last_name) from employees group by length(last_name) having count(*)>1;
65、按照多个字段分组
【1】两个条件分组 select avg(salary), department_id, job_id from employees group by department_id,job_id;
66、添加排序
【1】在65基础上，按照平均工资高低排序 select avg(salary), department_id, job_id from employees group by department_id,job_id order by avg(salary) desc 【2】也可以加条件 select avg(salary), department_id, job_id from employees where department_id is not null group by department_id,job_id order by avg(salary) desc 【3】加上平均薪资大于11334 select avg(salary), department_id, job_id from employees where department_id is not null group by department_id,job_id HAVING avg(salary)>11334 order by avg(salary) desc;
69、笛卡尔乘积
【1】连接查询：多表查询、当查询的字段来自于多个表 CREATE TABLE girl( id int(6) primary key, name varchar(20), boy_friend int(6)); -------------------- CREATE TABLE boy( id int(6) primary key, name varchar(20)); 【2】查询女明星和女明星的男朋友 select girl.name as gril_name, boy.name as boy_name from girl left join boy on girl.boy_friend_id=boy.id; 【3】内连接、外连接（左外连接、右外连接）、交叉连接 【4】sql199 支持内连接+外连接+交叉连接 【5】等值连接 select last_name,department_name from employees,departments where employees.department_id=departments.department_id; --------------------------------- select last_name,department_name from employees left join departments on departments.department_id=employees.department_id; 【6】表名太长的时候，为表名起别名 select last_name, department_name, commission_pct from employees e,departments d where e.department_id=d.department_id and e.commission_pct is not null; 【7】查询城市名第二个字符为肥的部门名和城市名 select department_name,city from departments d,locations l where d.location_id=l.location_id and city like '_肥%'; 【8】查询每个城市的部门个数 select count(*),city from departments d,locations l where d.location_id=l.id group by city; 【9】查询有奖金的部门名和部门领导编号和改部门的最低工资 select d.name department_name,d.manager_id,min(salary) from departments d,employees e where d.id=e.department_id and commission_pct is not null group by d.name,d.manager_id; 【10】三表连接，员工名 部门名 所在城市 select e.last_name,d.name,l.city from employee e left join department d on e.department_id=d.id left join location l on d.location_id=l.id;
73、非等值连接
【1】查询员工工资和工资级别 select e.salary,j.grade_level from employee e left join job_grade j on e.salary BETWEEN j.lowest_salary and j.highest_salary;
74、自连接
【1】说白了就是一个表查询两次。 select e.id employee_name,e.last_name employee_last_name,e1.id manager_id,e1.last_name manager_name from employee e left join employee e1 on e.manager_id=e1.id;
76、概念回顾
【1】升降序 asc desc 【2】排序列表 支持多个字段、过个字段、函数、表达式、别名 【3】order by放到最后,除limit之外 【4】函数 隐藏细节、提高可用性
78、SQL199语法
【1】左外、右外、全外 【2】select 查询列表 from 表1 left join 表2 on 连接条件 where筛选条件 select last_name,j.title from employee e left join jobs j on e.job_id=j.id where e.last_name like '%e%' 【3】结果上筛选部门员工>3的记录，并排序 select count(*),d.name from employee e left join department d on e.department_id=d.id group by d.name having count(*)>2 order by count(*) 【4】查询人名 部门名 工作名称 select employee.last_name,department.name,jobs.title from employee left join department on employee.department_id=department.id left join jobs on employee.job_id=jobs.id order by department.name desc
80、非等值连接
【1】查询员工的工资级别 select employee.salary,job_grade.grade_level from employee left join job_grade on employee.salary BETWEEN job_grade.lowest_salary and job_grade.highest_salary 【2】按照级别等于A的降序 select employee.salary,job_grade.grade_level from employee left join job_grade on employee.salary BETWEEN job_grade.lowest_salary and job_grade.highest_salary group by job_grade.grade_level HAVING grade_level = 'A' 【3】查询员工的名字、上级的名字 select employee.last_name,e.last_name from employee left join employee e on employee.manager_id=e.id 【4】查询没有男朋友的女生名 select girl.`name`,boy.* from girl left join boy on girl.boy_friend_id=boy.id
83、左右全连接
【1】左连接、右连接
【2】全连接、交叉连接
【3】查询部门名称为人力资源部或IT部门的员工信息
select
employee.last_name,employee.salary,department.name
from department
left join employee
on department.id=employee.department_id
where department.name in ('人力资源部','IT部门')
87、子查询
【1】位置 select后面、from后面、where或having后面、exists后面 【2】结果集：一行一列、一列多行、一行多列、多行多列 【3】子查询都是放在()内,一般放置在条件右侧 【4】谁的工资比Rose高 select employee.last_name,employee.salary from employee where employee.salary > (select employee.salary from employee where employee.last_name='Rose') 【5】返回公司工资最少员工的last_name,job_id和salary select employee.last_name,employee.salary,employee.job_id from employee where employee.salary = ( select min(employee.salary) from employee )
89、列子查询
【1】列子查询、一列多行 in 、not in、any、some、all 【2】返回location_id是1或2的部门中的所有员工姓名 select employee.last_name from employee where employee.department_id in ( select distinct department.id from department where department.location_id in(1,2) ) 【3】返回其他部门中比job_id为1的部门任一工资 select employee.id,employee.last_name,employee.job_id,employee.salary from employee where employee.salary < any (select DISTINCT employee.salary from employee where employee.job_id = 1 ) 【4】< any 就是小于结果集中最大的 【5】< all 就是小于结果集中最小的
91、SELECT后面的子查询
【1】查询每个部门的员工个数 select count(employee.last_name), department.name from employee left join department on employee.department_id=department.id group by employee.department_id ------------------------------- select department.name, ( select count(*) from employee where employee.department_id=department.id ) as '员工数' from department
92、放在FROM后面
【1】查询每个部门平均工资等级，就是查询的结果集充当一张表，要求这个表必须起别名 select avg_salary, department_id, job_grade.grade_level from ( select avg(employee.salary) as avg_salary, employee.department_id as department_id from employee group by employee.department_id ) as ag_dep,job_grade where avg_salary BETWEEN job_grade.lowest_salary and job_grade.highest_salary;
93、exists后面（相关子查询）
【1】有值返回1 select exists ( select employee.id from employee ) 【2】没有值返回0 select exists ( select employee.id from employee where employee.id=6000 ) 【3】查询员工名与部门名 select department.`name` from department where exists ( select * from employee where employee.department_id=department.id ) 【4】mysql查询不混乱的原则就是都加上表名或则表名的简写去查询 ------------------------------------------------------------ 【5】查询没有女朋友的男生 select boy.* from boy where boy.id not in ( select girl.boy_friend_id from girl where girl.boy_friend_id is not null )
95、分页查询
【1】需要分页提交SQL请求 【2】查询前两条员工信息 select employee.first_name,employee.last_name from employee limit 0,2
97、知识复习
【1】内连接 inner join 【2】外链接 left join 【3】交叉连接 【4】子查询
100、联合查询
【1】union 联合、合并 select * from employee where employee.email like '%4%' union select * from employee where employee.department_id > 1 【2】使用场景：查询中国男性与外国男性用户信息，查询多个表时 【3】union all不去重
102、数据的插入
【1】语法
insert into 表(列名...) values(值...)
【2】往女生表新增数据
insert
into girl(id,name)
values(4,'佟丽娅')
【3】设置id自增后
insert
into girl(name)
values('高圆圆')
【4】列的顺序要和值对应，顺序可以不与表格的顺序一致
【5】字段尽量不要省略，不然values必须写全
insert
into girl
values(9,'赵敏',null)
【6】用表名的另一种方式
insert
into girl
set
name='刘涛',
boy_friend_id=3
104、两种方式比较
【1】第一种方式可以插入多行，这个在批量操作的时候用到了，AHDayTable。方式二不支持
----------------------------------------------------------------------------------------
insert
into girl(name)
values('张一萌'),
('李亚茹'),
('田伟丽');
【2】方式一支持子查询，方式二不支持
105、修改语句
【1】语法 update 表名 set 列=新值,列=新值 where 条件 update girl set name='李亚茹的伙伴' where girl.name='李亚茹' 【2】修改多个 update girl set name='李亚茹' where girl.name like '%的%' 【3】修改没有男朋友的女生的，默认男朋友 update girl left join boy on boy.id=girl.boy_friend_id set girl.boy_friend_id=4 where girl.boy_friend_id is null
107、删除语句
【1】语法 delete from 表名 where 筛选条件，支持单表与多表删除 delete from girl where girl.id=15 【2】删除张无忌女朋友的信息，也可以用子查询删 delete temp_girl from girl temp_girl left join boy on boy.id=temp_girl.boy_friend_id where boy.name='张无忌' -------------------------------- delete from girl where girl.boy_friend_id= ( select boy.id from boy where boy.name='小哥' )
109、删除方式二
【1】truncate语句，清空数据，而且自增长会从1开始，6666 truncate table boy;//全部删除 【2】效率比delete from 高
111、DDL语言概述
【1】库的管理 创建 create 、修改alter、删除drop、查看 show databases; 【2】库的创建 create database books; ----------------------- create database if not exists books;// 优化不存在才创建 【3】库的修改，更改字符集 alter database books character set utf8mb4 【4】库的删除 drop database books if exists books;
113、库下面表的管理
【1】表的创建 create table 表名(列名 列的类型（长度、约束）,列名 列的类型（长度、约束）...) 【2】创建一个book表 create table book( id int(6), name varchar(20), price double, author_id int(6), publish_date datetime ); 【3】查看表信息 create table author ( id int(6), name varchar(20), country varchar(20) ) 【4】desc author
114、表的修改
【1】修改列名、修改累的类型、约束、删除列、修改表名 alter table book change column publish_date pub_date datetime; ---------------------------------------------------- desc book 【2】修改列的类型 alter table book modify column pub_date timestamp ---------------------------- desc book 【3】增加列 alter table book add column year_salary double; -------------------------------- desc book 【4】删除列 alter table author drop column year_salary ------------- desc author 【5】修改表名 alter table author rename to book_author; 【6】总结 alter table 表名 add | drop | modify | change + column
115、表的删除
【1】删除表 drop table book_author; -------------------- show tables;
116、表的复制
【1】数据插入 insert into author(id,name,country) values (1,'平凡的世界','中国'), (2,'十八岁出门远行','中国'), (3,'活着','中国') 【2】复制表结构 create table copy_table like author 【3】复制表结构与数据 create table author_copy select * from author; 【4】同时可以选择性复制、复制空列等
117、表和库的管理
【1】建立表 use study; ---------------- create table author ( id int(6), name varchar(20), country varchar(20) )
118、常见的数据类型
【1】整形：小数、浮点型、浮点数 【2】字符型： 较短的文本 varchar、较长的文本 text blob 【3】日期型
119、整形
【1】tinyint 1、smallint 2、mediumint 3、int 4、bigint 8 【2】特点以int型为例子： 默认支持正负数 create table tab_int ( ti int ) ---------------------- 支持有符号 insert into tab_int values (-1) 【3】无符号，则t2不能插入-数 create table tab_int ( t1 int, t2 int UNSIGNED ) 【4】如果插入超出范围，则报错Out of range value for column 't1' at row 1 insert into tab_int values (12345678912222222222222222222222222222222,1) 【5】int(6) 并不影响放入多少数值的长度，范围是有tinyint、samllint这种类型决定的，搭配zerofill，如果 长度不够，则前面用0填充
120、小数
【1】浮点型 float 4 , double 8 float(M,D) double(M,D) 【2】定点性： dec(M,D) decimal(M,D) 【3】特点 create table tab_float( f1 float(5,2), f2 double(5,2), f3 decimal(5,2) ) ------------------------------- insert into tab_float values (123.45,123.45,123.45) 【4】M代表的是整数部位+小数部位，D代表小数部位，如果超过范围报错或将会插入临界值 【5】M D都可以省略，如果是decimal 则默认10 D 默认0 。如果是float和double,则会根据插入值的精度来 【6】定点型精确度较高，如果需要插入数值的精度较高如货币运算则考虑使用，其他的可以用float double ---------------------------------------------------------------------- 【7】原则：所选择的类型越简单越好,而且
121、字符型
【1】短文本 char varchar
【2】text 较长的文本
【3】较大的二进制blob
【4】char(M) varchar(M) 最多的字符数。char固定长度字符、varchar可变长度字符。
在char上效率稍微高点、varchar稍微低点。
【5】enum 枚举
create table tab_char(
c1 enum('a','b','c')
)
---------------------
insert into tab_char values('a');
insert into tab_char values('c');
insert into tab_char values('m');
insert into tab_char values('A');
【6】set类型 集合
create table tab_set(
s1 set('a','b','c','d')
)
-------------
insert into tab_set values('a');
insert into tab_set values('c');
insert into tab_set values('m');
【7】binary和varbinary用于保存较短的二进制
122、日期型
【1】date 4 只能保存年月日 【2】datetime 8 保存年月日时分秒 【3】timestamp 4 时间戳 【4】time 3 -838:59:59 到 838:59:59 【5】year 1 年 【6】实例 create table tab_date( t1 datetime, t2 timestamp ) ------------------ insert into tab_date values(now(),now())
124、知识复习
【1】联合查询 union 、多次查询结果合并成一个结果 union [all] 【2】适用于查询多个表，查询的列基本是一致 【3】查询模板 select 查询列表 from 表1 left join on 连接条件 where 筛选 group by 分组列表 having 筛选 order by 排序 limit 分页查数据 【4】DML语言 insert into 表名（字段名） values(值) 【5】加入表中有可以为null的字段 字段和值都可以省略、字段写上使用null值插入 【6】字段和值的个数必须一致。字段名可以省略，但是默认是所有列 【7】方式二 insert into 表 set 字段=值.... 【8】方式一支持批量操作 insert into 表（字段名） values(值...),(值...) 【9】修改表的记录 update 表名 set 字段=值 where 筛选条件 【10】删除 delete truncate delete from 表1,表2 left join on 表2 on 连接条件 where 筛选条件 delete 也可以搭配limit使用 【11】库的创建、删除。表的创建、修改、删除 create database 库名; --------------------------- drop database 库名; --------------------------- create table 表名（字段名 字段类型[约束]，字段名 字段类型[约束]....） --------------------------- 修改表-添加列 alter table 表名 add column 列名 类型 [first| after 字段名]; --------------------------- 修改列的类型与约束 alter table 表名 modify 列名 新类型[约束] --------------------------- 删除某一列 alter table 表名 drop column 列名; --------------------------- 修改表名 alter table 表名 rename 表名 --------------------------- drop table 表名; --------------------------- 复制表 create table 新表 like 旧表; create table 表名 select 查询列表 from 旧表[where 筛选] 【12】数据类型：整形 浮点型 日期型
125、常见约束概述
【1】约束代表限制、用于限制表中的数据，为了保证我们表中数据的准确和可靠性 【2】特别是在数据唯一性保证上 【3】约束的类型：6大约数 NOT NULL：非空约束 DEFAULT 默认 用于保证该字段有默认值 PRIMARY key 主键，保证唯一性且非空 unique 唯一，用于保证该字段的值具有唯一性，可以为空 check 检查约束，mysql不支持 foreign key 外键，现在不常用了 【4】添加约束的时机：创建表时、添加表时 【5】表级约束除了非空、默认都支持
126、创建表时添加约束
【1】创建带约束的表 create table major( id int primary key, major_name varchar(20) ) -------------------------- create table stu_info( id int(6) primary key, stu_name varchar(20) not null, gender char(1) check(gender='男' or gender='女'), seat int unique, age int default 18, major_id int references major(id) ) 【2】desc stu_info 查看表信息
127、添加表级约束
【1】删除表 drop table stu_info 【2】创建表级约束 create table stu_info( id int, name varchar(20), gender char(1), seat int, age int, major_id int, constraint pk primary key(id), constraint uq unique(seat), constraint ck check(gender='男' or gender='女'), constraint fk_stu_info_major foreign key(major_id) REFERENCES major(id) ) ----------------------------- 也可以不用起名字 create table stu_info( id int, name varchar(20), gender char(1), seat int, age int, major_id int, constraint primary key(id), constraint unique(seat), constraint check(gender='男' or gender='女'), constraint foreign key(major_id) REFERENCES major(id) ) 【3】通用写法 create table stu_info( id int primary key, name varchar(20) not null, gender char(1), seat int unique, age int DEFAULT 18, major_id int, constraint fk_stu_info_major foreign key(major_id) REFERENCES major(id) )
128、主键和唯一的区别
【1】主键只能有一个，唯一可以有多个 【2】组合主键，组合在一起不能重复就可以，不推荐使用
129、外键的特点
【1】要求在从表设置外键关系 【2】从表的键列的类型要和主表的类型、约束一致或兼容 【3】主表的关联列必须是一个key（一般是主键或唯一键） 【4】要求插入数据时，应该先插入主表。删除数据先删除从表。 【5】一个列 可以有多个约束。
130、修改表时添加约束
【1】修改某个列约束 alter table stu_info modify column stu_info.name varchar(20) not null -------------------------- alter table stu_info modify column stu_info.name varchar(20) null 【2】表级约束 alter table stu_info add UNIQUE(seat) 【3】表级外键 alter table stu_info add foreign key(major_id) REFERENCES major(id)
131、修改表时删除约束
【1】删除非空约束 alter table stu_info modify column stu_info.name varchar(20) null 【2】删除默认值 alter table stu_info modify column age int; 【3】删除主键 alter table stu_info modify column id int; ---------------------------- alter table stu_info drop primary key; 【4】删除唯一键 alter table stu_info drop index seat; 【5】删除外键 alter table stu_info drop foreign key fk_major_id_major;
133、标识列：自增长列
【1】不用手动插入值,系统自动提供增长值
create table tab_identity(
id int primary key auto_increment,
name varchar(20)
)
【2】插入值，则id自动增长
insert into tab_identity(name) values('小明')
【3】查看自增情况
show VARIABLES like '%auto_increment%'
【4】一个表最多只能有一个自增列
【5】自增列的类型 一般是int 整形
【6】修改表时设置自增列
alter table tab_identity modify column id int primary key auto_increment
【7】删除自增
alter table tab_identity modify column id int;
134、事务控制
【1】TCL 事务控制语言 【2】一个或一组sql语句组成的执行单元,这个执行单元要么全部执行、要么全部不执行 【3】转账： 张三丰1000 郭襄1000 update 表 set 张三丰的余额=500 where name='张三丰'; 如果中间发生意外 update 表 set 郭襄的余额=1500 where name='郭襄'; 【4】show ENGINES查看，InnoDB这个存储引擎是支持事务的 【5】事务的特点：ACID （1）原子性 Atomicity （2）一致性 Consistency （3）隔离性 Isolation （4）持久性 Durability
135、事务的创建
【1】隐式的事务 insert update delete语句 【2】显式事务，事务具有明显的开启和结束的标记。 show variables like 'autocommit' 前提：先设置自动提交功能为禁用 -------------------------------- （1）set autocommit=0; （2）start transaction; 可选的，会默认开启 -------------------------------- show variables like 'autocommit' （3）写sql语句 增删改查 （4）结束事务 （5）提交事务 commit （6）rollback 回滚事务 【3】演示事务的使用 #开启事务 set autocommit=0; start transaction; update account set account.balance =500 where account.name='张三丰'; update account set account.balance =1500 where account.name='郭襄'; #结束事务 commit; 【4】回滚 #回滚 rollback;
136、事务的并发问题概述
【1】事务并发-类似多线程: 【2】脏读: 看到余额，但是是没有提交的，临时的 【3】不可重复读：由于是临时的，所以不可重复读 【4】幻读 【5】设置数据库事务的隔离级别来解决以上问题 （1）select @@tx_isolation; （2）set session transaction isolation level read uncommitted （3）这种情况多个客户端连接sql就会出现上面的问题 （4）四个级别可以解决不同的问题 read uncommited ：出现脏读、幻读、不可重复度 read commited：避免脏读 repeatable read: 避免脏读、不可重复度 serializable : 都能避免
138、回滚点演示
【1】这样只会删除第一个 set autocommit=0; delete from account where id=1; SAVEPOINT temp; delete from account where id=2; rollback to temp; COMMIT;
139、视图的概述
【1】视图：虚拟表。 mysql 5.1出现的新特性，通过普通表动态生成的数据 【2】临时数据，类似学校游泳队、平时在每个班级，有比赛组成一个队 select girl.name,boy.name from girl left join boy on girl.boy_friend_id=boy.id where girl.name like '%李%'; 【3】视图 create view v1 as select girl.name,boy.name as boy_friend_name from girl left join boy on girl.boy_friend_id=boy.id where girl.name like '%李%'; -------------------------------------------- 后面的查询可以变为： select * from v1 where name like '%李%';
140、视图的创建
【1】create view 视图名 as 查询语句; 【2】简化sql操作,封装查询细节,保护数据，提高安全性
141、视图的修改
【1】create or replace view 视图名 as 查询语句。如果存在则修改、不存在创建 【2】alter view 视图名 as 查询语句; //第二种方式 【3】视图的查询与删除 drop view 视图名,视图名; 可以批量删除 【4】desc v1;
144、视图的更新、视图与表的对比
【1】insert into v1 values('李Y','L')
【2】view 与table
【3】表占用了空间,视图仅占用了逻辑部分
149、变量的概述
【1】与JAVA一样的概念 【2】系统变量：全局变量、会话变量 【3】自定义变量：用户变量、局部变量
150、系统变量
【1】使用语法 （1）查看所有的系统变量 show global VARIABLES; show session VARIABLES; （2）查看满足条件的部门系统变量 show global VARIABLES like '%char%' （3）为系统变量赋值 set 系统变量名 = 值
152、会话变量
【1】查看所有的会话变量 show session VARIABLES; 【2】查看部分 show session VARIABLES like '%char%' 【3】查看某一个 select @@character_set_client; 【4】为某个会话变量赋值 set @@character_set_client=值;
153、用户变量
【1】变量是用户自定义的，同于会话变量的作用域 【2】初始化 set @用户名变量名=值; 【3】更新用户变量值 set @用户名变量名=值; // 就是再来一遍 set @wdfgdzx='刘新雷'; select @wdfgdzx;
154、局部变量
【1】仅仅在begin and中有效 【2】declare temp int; 【3】set temp = 值; 【4】使用 select 局部变量名
155、存储过程概述
【1】类似JAVA中的方法。一组预先编译好的SQL语句集合 【2】减少编译次数，提高了效率。 【3】语法 创建、使用 create procedure 存储过程名(参数列表) begin 存储过程提（一组SQL语句） end 【4】参数类表包含三部分 参数模式 参数名 参数类型 （1）模式 in out inout （2）如果存储过程提仅仅只有一句话，begin end 可以省略 【5】调用语法 CALL 存储过程名
157、空参的存储过程
【1】空参列表
delimiter $
create procedure my_pro()
begin
insert into boy(name)
values ('张飞'),('关羽'),('赵云'),('黄忠'),('马超')
end $
【2】函数里就可以看到my_pro
【3】调用
CALL my_pro
158、创建带in模式的存储过程
【1】根据女神名查询对应的男神信息
delimiter $
create procedure my_pro2(in girl_name varchar(20))
begin
select * from boy
left join girl
on boy.id=girl.boy_friend_id
where girl.name= girl_name;
end $
【2】调用
call my_pro2('李亚茹')
159、创建带out模式的存储过程
【1】根据女神名返回男神名
delimiter $
create procedure my_pro3(in girl_name varchar(20),out boy_name varchar(20))
begin
select boy.name into boy_name
from boy
left join girl
on boy.id=girl.boy_friend_id
where girl.name= girl_name;
end $
【2】调用
set @boy_name = '';
call my_pro3('李亚茹',@boy_name);
select @boy_name;
160、带inout模式参数的存储过程
【1】传出a、b 并返回a、b delimiter $ create procedure my_pro4(inout a int(20),inout b int(20)) begin set a = a*2; set b= b*2; end $ 【2】调用 set @m=10; set @n=20; call my_pro4(@m,@n); select @m,@n;
162、存储过程删除
【1】删除 drop procedure 函数名称 【2】查看存储过程（函数） show create procedure my_pro
165、函数的概述
【1】函数只能有一个返回，有且仅有一个 【2】函数的创建，函数体仅有一句话，可以省略begin end 【3】返回男生的个数 delimiter $ create function my_fun1() returns int begin declare result int default 0; select count(*) into result from boy; return result; end $ select my_fun1()$ 【4】有参数有返回，根据男生id返回男生姓名 delimiter $ create function my_fun2(id int) returns varchar(20) begin declare result varchar(20); select boy.name into result from boy where boy.id=id; return result; end $ select my_fun2(2)$
168、函数的查看与删除
【1】查看函数 show create function my_fun1 【2】删除函数 drop function my_fun1
170、流程控制结构
【1】顺序结构: 从上往下执行 【2】分支结构: 程序从两条或多条路径中选择一条去执行 【3】循环结构: 程序在满足一定条件的基础上，重复执行一段代码
171、分支结构与循环结构
【1】if函数 if(表达式1，表达式2，表达式3）。执行顺序 如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值。 【2】case结构 （1）类似于java中的switch语句，一般用于等值判断。 case 变量|表达式|字段 when 判断 then ... （2）类似java的多重if语句，一般用于实现区间判断。 case when 条件1 then 语句 when 条件2 then 语句 else 语句; end case;
173、if结构
【1】if 条件1 语句1; elseif 条件2 then 语句2; ... else 语句n; end if; 【2】应用在begin end 中，在函数中使用。
174、循环
【1】while loop repeat
【2】循环控制：iterate 类似continue,结束本次循环，继续下一次。
leave 类似于break,跳出，结束当前循环。
【3】while的写法，根据设置的次数插入多条记录
delimiter $
create procedure pro_while(in insert_count int)
begin
declare i int default 1;
while i<=insert_count do
insert into boy(name) values('诸葛亮');
set i=i+1;
end while;
end $
call pro_while(100); // 调用插入100个诸葛亮
177、流程控制经典案例
【1】想表中插入指定个数的、随机字符串 delimiter $ create procedure test_insert(in insert_count int) begin declare i int DEFAULT 1; declare str varchar(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz'; declare start_index int DEFAULT 1; declare len int DEFAULT 1; while i<= insert_count do set start_index=floor(rand()*26 +1); set len=floor(rand()*(26-start_index+1) +1); insert into boy(name) values(substr(str,start_index,len)); set i=i+1; end while; end $ call test_insert(100)
178、小结
【1】系统变量 【2】自定义变量 【3】存储过程体与函数 【4】流程控制结构 顺序结构、分支结构、循环