1 基础
1）mysql存储结构： 数据库 -> 表 -> 数据 sql语句
mysql -u root -p; root
2）管理数据库：
增加： create database 数据库 default character utf8;
删除： drop database 数据库;
修改： alter database 数据库 default character gbk;
查询： show databases / show create database 数据库;
3) 管理表：
选择数据库：use 数据库;
增加： create table 表(字段名1 字段类型,字段名2 字段类型......);
删除： drop table 表;
修改：
添加字段： alter table 表 add [column] 字段名 字段类型;
删除字段: alter table 表 drop [column] 字段名;
修改字段类型： alter table 表 modify 字段名 新的字段类型;
修改字段名称 ： alter table 表 change 旧字段名 新字段名 字段类型;
修改表名称： alter table 表 rename [to] 新表名;
查询： show tables / desc student;
4) 管理数据:
增加： insert into 表(字段1，字段2,。。。) values(值1，值2.。。。。);
删除： delete from 表 where 条件;（另一种）truncate table 表 -----将约束条件重置
修改： update 表 set 字段1=值1，字段2=值2...... where 条件;
查询：
4.1）所有字段： select * from 表;
4.2）指定字段： select 字段1,字段2.... from 表;
4.3)指定别名： select 字段1 as 别名 from 表;
4.4 )合并列： select (字段1+字段2) from 表;
4.5)去重： select distinct 字段 from 表;
4.6)条件查询：
a)逻辑条件 ：and(与) or（或）
b）比较条件： > < >= <= = <> between and(在。。。之间)
c）判空条件： 判断null： is null / is not null
判断空字符串： ='' / <>''
d）模糊条件： like %: 替换任意个字符 _: 替换一个字符
4.7 分页查询：limit 起始行,查询行数 起始行从0开始
4.8 排序： order by 字段 asc/desc
asc: 正序，顺序
desc：反序，倒序
4.9 分组查询：group by 字段
4.10: 分组后筛选： having 条件
SQL语句的分类：
DDL： 数据定义语言
create / drop / alter
DML：数据操作语句
insert / delete /update / truncate
DQL： 数据查询语言：
select / show
2 数据约束
2.1 默认值
default
注意：
1）对默认值字段插入null是可以的。
2）对默认值字段可以插入非null
2.2 非空
not null
注意：
1）非空字符必须赋值
2）非空字符不能赋null
2.3 唯一
unique
注意：
1）唯一字段可以插入null
2）唯一字段可以插入多个null
2.4 主键
primary key
作用： 非空+唯一
注意：
1）通常情况下，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段。
2.5 自增长
auto_increment
作用： 自动递增
2.6 外键
foreign key
作用：约束两种表的数据
-- 声明一个外键约束
CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
约束 外键名称 外键 参照 参考表(参考字段)
外键
多表的设计
一对多：(非常多)
CREATE TABLE department(
id int primary key,
name varchar(100),
floor int
);
CREATE TABLE employee(
id int primary key,
name varchar(100),
salary float(8,2),
dept_id int,
CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);
多对多：（引入第三个关系表）
CREATE TABLE teacher(
id int primary key,
name varchar(100),
salary float(8,2)
);
CREATE TABLE student(
id int primary key,
name varchar(100),
grade varchar(100)
);
CREATE TABLE teacher_student(
t_id int,
s_id int,
PRIMARY KEY(t_id,s_id),
CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),
CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id)
);
一对一：（开发中很少用）
CREATE TABLE person(
id int primary key,
name varchar(100)
);
CREATE TABLE idcard(
id int primary key,
num varchar(18),
CONSTRAINT p_id_fk FOREIGN KEY(id) REFERENCES person(id)
);
2.7 级联操作
级联修改： ON UPDATE CASCADE
级联删除： ON DELETE CASCADE
-- 声明一个外键约束
CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
-- 外键名称 外键 参考表(参考字段)
3 三大范式
设计原则： 建议设计的表尽量遵守三大范式。
第一范式： 要求表的每个字段必须是不可分割的独立单元
第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。
第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。
4 关联查询(多表查询)
4.1 内连接查询：只有满足条件的结果才会显示(使用最频繁)
SELECT empName,deptName -- 2）确定哪些哪些字段
FROM employee,dept -- 1）确定查询哪些表
WHERE employee.deptId=dept.id -- 3）表与表之间连接条件
-- 内连接的另一种语法
SELECT empName,deptName
FROM employee
INNER JOIN dept
ON employee.deptId=dept.id;
4.2左[外]连接查询： 使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null
-- （注意： 左外连接：左表的数据一定会完成显示！）
SELECT d.deptName,e.empName
FROM dept d
LEFT OUTER JOIN employee e
ON d.id=e.deptId;
4.3 右[外]连接查询: 使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null
-- （注意： 右外连接：右表的数据一定会完成显示！）
SELECT d.deptName,e.empName
FROM employee e
RIGHT OUTER JOIN dept d
ON d.id=e.deptId;
4.4自连接查询
SELECT e.empName,b.empName
FROM employee e
LEFT OUTER JOIN employee b
ON e.bossId=b.id;
5 存储过程
5.1-- 创建存储过程
DELIMITER $ -- 声明存储过程的结束符
CREATE PROCEDURE pro_test() --存储过程名称(参数列表)
BEGIN -- 开始
-- 可以写多个sql语句; -- sql语句+流程控制
END $ -- 结束 结束符
5.2-- 执行存储过程
CALL pro_test(); -- CALL 存储过程名称(参数);
参数：
IN： 表示输入参数，可以携带数据带存储过程中
OUT： 表示输出参数，可以从存储过程中返回结果
INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能
5.3-- ***mysql的变量******
-- 全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）
-- 查看所有全局变量： show variables
-- 查看某个全局变量： select @@变量名
-- 修改全局变量： set 变量名=新值
-- character_set_client: mysql服务器的接收数据的编码
-- character_set_results：mysql服务器输出数据的编码
-- 会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
-- 定义会话变量: set @变量=值
-- 查看会话变量： select @变量
-- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！
-- 定义一个局部变量
declare i INT default 1;-- 定义一个局部变量i为整形，默认值为1
5.4 带有条件的存储过程
if 条件 then
执行语句;
elseif 条件 then
执行语句;
...
else
执行语句;
end if;
5.5 带有循环语句的存储过程
while 条件 do
执行语句（没有自增）;
end while;
6 触发器
6.1-- 创建触发器(添加)
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW -- 当往员工表插入一条记录时
INSERT INTO test_log(content) VALUES('员工表插入了一条记录');
6.2-- 创建触发器(修改)
CREATE TRIGGER tri_empAdd AFTER UPDATE ON employee FOR EACH ROW -- 当往员工表插入一条记录时
INSERT INTO test_log(content) VALUES('员工表插入了一条记录');
6.3-- 创建触发器(添加)
CREATE TRIGGER tri_empAdd AFTER DELETE ON employee FOR EACH ROW -- 当往员工表插入一条记录时
INSERT INTO test_log(content) VALUES('员工表插入了一条记录');
7 备份
mysqldump -uroot -p day17 > c:/bak.sql
恢复
mysql -uroot -p day17 < d:/back.sql
注意
不需要登陆
8GRANT 权限 ON 数据库.表 TO '账户名'@'账户类型' IDENTIFIED BY '密码';
举例：
权限： select insert delete update drop create/ 或,all
flush?privileges
-- 修改密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
-- 分配权限账户
GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
面试题
int(3)和int的区别？ create table t (t int(3) zerofill);
如何快速构建相同表结构？ create table stu like users;
