一、数据库操作 DDL 语句
命令行执行 sql 语句必须以分号 “;” 结束，否则出现 " -> "
1.1 开启 MySQL 服务，登录数据库
mysql -uroot -p 数据库密码
1.2 创建数据库
create database go_db;
// 不指定则使用默认字符集
create database xx character set 'utf8';
// 修改数据库使用的字符集
alter database xx character set 'gbk';
1.3 打开数据库
use go_db;
1.4 删除数据库
drop database go_db;
二、数据库表操作 DML 语句
2.1 创建表
create table user_tbl(
id integer primary key AUTO_INCREMENT,
username varchar(20),
password varchar(20)
);
//基于现有的表，复制表结构和数据到新的表，如果不复制数据添加where条件，如id = -1
create table stu_tbl as select name,age,xx from person;
//添加表字段，默认添加到最后一个位置，可指定位置 afer，first
alter table 表名 add 字段名 字段类型 ;
//重命名一个字段
alter table 表名 change 旧名 新名;
//修改一个字段数据类型
alter table 表名 modify 字段名 字段类型 ;
//删除一个字段
alter table 表名 drop column 字段名;
//表名重命名
rename table 表名 to 新名 ;
2.2 显示当前数据库都有哪些表
show tables;
2.3 查看表结构
desc user_tbl;
2.4 表的插入操作 insert 用法
// 一次插入 1 条数据
insert into user_tbl (username,password)values("tom","123");
// 一次插入多条数据
insert into user_tbl (username,password)values("tom","123"),("jack"."456");
// 将查询的数据插入表中,字段要一一对应,查询的 B 表插入到 A 表
insert into user_tbl (username,password) select username,password from emp;
2.5 表的更新操作
// 一般都会有where，否则更新的是全部表信息
update 表名 set username = "smith",password = "789"
where id = 5;
// 一般会有where，否则会删除整张表
delete from 表名
where id = 2 //一般会有where，否则会删除整张表
2.6 删除表
drop table user_tbl;
2.7 清空表
truncate table user_tbl;
2.8 COMMIT 和 ROLLBACK的使用
COMMIT：提交数据，一旦执行数据被永久的保存在数据库中，意味着不可以回滚。
ROLLBACK：回滚数据，一旦执行可以实现数据的回滚，回滚到最近的一次COMMIT后。
1.DDL的操作一旦执行，不可以回滚。
2.DML的操作一旦执行，默认不可以回滚，但设置 set autocommit = false 就可以实现回滚。
COMMIT;
select * from 表名；
set autocommit = false;
// DML: delete 删除表，数据可以回滚
// DDL：truncate 清空表表,数据不可以回滚
delete from 表名;
select * from 表名
ROLLBACK;
三、登录时权限被拒绝
3.1 设置免密登录，先关闭 mysql 服务。
sudo su
mysqld_safe --user=mysql --skip-grant-tables --skip-networking
3.2 修改密码 xuyq0103
use mysql;
FLUSH PRIVILEGES;
ALTER user 'root'@'localhost' IDENTIFIED BY 'newpassward';
quit;
四、关闭 mysql server
Macbook 系统偏好设置，手动无法关闭
ps -ef | grep mysqld
kill pid
五、单表查询
// 查询顺序，先表---where---select---order by---page
SELECT * From 表名
WHERE 字段名 = xxx
ORDER BY 字段名
DESC LIMIT （pageNum - 1）* pageSize,pigeSize; //第一个参数代表偏移多少，第二个参数页大小
// 模糊查询,第二个字母是a，%代表任意
SELECT * FROM 表名 WHERE name LIKE '_a%';
六、多表查询 重点
避免笛卡尔积错误，n个表查询进行n-1次连接。
建议多表查询时，字段前加上表名。
内连接：两个表连接满足连接条件的所有行
左连接：两个表连接除了满足连接条件以外的行，还返回左表中不满足条件的行
右连接：两个表连接除了满足连接条件以外的行，还返回右表中不满足条件的行
// 等值连接，非自连接
SELECT 表1.字段名，表2.字段名
FROM 表1 ，表2，表3
WHERE 表1.某字段名 = 表2.某字段名
AND 表2.字段名 = 表3.字段名
//自连接，自己连接自己
SELECT 别名.字段名，别名.字段名
FROM 表1名 别名，表1名 别名
WHERE 别名.字段名 = 别名.字段名
//以上是SQL92支持的内连接，SQL99另外一种方式
SELECT 表1.字段名，表2.字段名，表3.字段名
FROM 表1 JOIN 表2 //使用 JOIN 连接表
ON 表1.字段名 = 表2.字段名 //ON 表示连接条件
AND 表2 JOIN 表3
ON 表2.字段名 = 表3.字段名
WHERE 表1.字段名 = xx
//左连接
SELECT 表1.字段名，表2.字段名
FROM 表1 LEFT JOIN 表2
ON 表1.字段名 = 表2.字段名
//右连接
SELECT 表1.字段名，表2.字段名
FROM 表1 RIGHT JOIN 表2
ON 表1.字段名 = 表2.字段名
七、函数应用
7.1 单行函数
输入一条数据，输出也是一条数据。
比如时间日期函数，字符串函数，数值型函数
7.2 聚合函数 SUM，AVG，MAX，MIN，COUNT
输入多条数据，输出一条数据
// 求表中有多少条记录，COUNT 会自动过滤 NULL,即发现 NULL 不计数
SELECT COUNT(*) FROM 表名 //COUNT(1),COUNT(0)都可以
// AVG = SUM / COUNT 三个函数都过滤 NULL
// 求平均值时需要注意
SELECT SUM(字段名) / COUNT(*) FROM 表名 //正确
// 错误，如果字段有NULL会过滤掉,分母不对，所以错误
SELECT AVG(字段名) FROM 表名
7.3 GROUP BY
- 对一张表进行分组，分组查询，比如把一张表中同一部门的员工分为一组，共分为多个组
// select 中的出现的非组函数的字段必须出现在 group by 中
// 查询各部门的平均工资
SELECT dep_id，AVG(salary) From 表名 GROUP BY dep_id;
// 语句位置
SELECT * FROM xx WHERE xx GROUP BY xx ORDER BY xx LIMIT xx;
7.4 HAVING
- 如果过滤条件中（即where 后边的条件）想要使用聚合函数，那么必须用 HAVING 替代 WHERE ,否则报错。WHERE 后不能使用聚合函数。
- HAVING 使用必须在 GROUP BY 后面。
// 各个部门中最高工资大于10000工资的部门信息
SELECT dep_id MAX(salary)
FROM 表名
GROUP BY dep_Id
HAVING MAX(salary) > 10000
八、SQL 执行顺序
//第二步执行
SELECT ...,....,....,...., //存在聚合函数
//第一步执行，从上到下
FROM ...(LEFT/RIGHT)JOIN ...ON ...
AND JOIN...ON... //多表连接条件
WHERE ... //不存在聚合函数
GROUP BY ... //分组条件
HAVING ... //存在聚合函数
//第三步执行，从上到下
ORDER BY ... //排序
LIMIT ... //分页
九、子查询
嵌套查询，两个查询语句可以合并为一句查询，增加查询效率。
9.1 单行子查询
内查询为固定值，也是不相关子查询。
单行比较操作符 > ，= ， >= ， < ， <= ，!=
SELECT 字段名
FROM 表名
WHERE salary > (
SELECT salary
FROM 表名
WHERE name = '张三'
);
9.2 多行子查询
内查询为多行数据
多行操作符
- IN ：等于列表中
任意一个值 - ANY ：和子查询返回的
某一个值比较，某一个满足就成立。 - ALL ：和子查询返回的
所有值比较，全部满足才成立。 - SOME：(同ANY)
SELECT name
FROM emp
WHERE salary IN ( //等于5000，6000,7000....
SELECT MIN(salary)
FROM emp
GROUP BY dep_id
)
9.3 相关子查询
按照一行一行的顺序执行，主查询的每一行都执行一次子查询
执行流程：
- 从主查询中取一条记录，一行一行的取，然后送进子查询。
- 子查询使用主查询送进去的一行记录，经过过滤筛选出满足条件的记录。
- 最后将送进去的记录与子查询满足条件的记录作比较，满足则输出。
例：查询员工工资大于本部门平均工资的员工name，salary，dep_id。
SELECT name,salary,dep_id
FROM emp e1
WHERE salary > (
SELECT AVG(salary)
FROM emp e2
WHERE e1.dep_id = e2.dep_id
)
//取第一个人的记录
//拿到第一个人的部门 id，计算该部门的平均工资
//对比第一个的工资该部门的平均工资是否满足
//方式2
SELECT name,salary,dep_id
FROM emp e1,
(SELECT AVG(salary) avg_salary
FROM emp
GROUP BY dep_id) t_avg //相当于一张含有部门平均工资的表
WHERE e1.salary > t_avg.avg_salary
AND e1.dep_id = t_avg.dep_id
十、约束
- 为什么需要约束？
保证数据的完整性。 - 什么叫约束？
对表中字段的限制。
1.非空约束 NOT NULL
CREATE TABLE stu(
id INT NOT NULL,
name VARCHAR NOT NULL
)
2.唯一性约束 UNIQUE
1.限制某个字段/某列的值不能重复
2.允许出现多个 NULL 值，即可重复添加空值。
3.添加唯一性约束的列上也会自动创建唯一索引，与约束名同名，删除约束只能通过删除索引。
CREATE TABLE stu(
id UNIQUE,
tel_num char(11) UNIQUE //不指定约束名，会与列名同名
email VARCHAR(25)
password VARCHAR(25)
//表级约束，别名，约束字段名,可复合约束
CONSTRAINT uk_email_pwd UNIQUE(email,password)
)
//修改约束
ALTER TABLE stu
MODIFY last_name VARCHAR(15) UNIQUE
ALTER TABLE stu
ADD CONSTRAINT uk_salary UNIQUE(salary)
//删除约束
ALTER TABLE stu
DROP INDEX last_name
3.主键约束 PRIMARY KEY
1.一个表对应一个主键约束，可以对应多个列。
2.特征：非空且唯一
CREATE TABLE stu(
id PRIMARY KEY AUTO_INCREMENT //自增长，添加数据的时候就不用添加了
name VARCHAR(15)
)
4.外键约束 FOREIGN KEY
应用场景：
先创建主表部门表，再创建从表员工表，员工表的部门id创建外键到部门表的主键部门id，此时如果添加员工，员工表的部门id，在部门表中不存在则不允许添加。
阿里巴巴，强制不使用外键，不适合分布式，高并发集群，存在数据库更新风险，影响插入速度。
CREATE TABLE dep(
dep_id INT PRIMARY KEY
dep_name VARCHAR(15)
)
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT
name VARCHAR(15)
emp_dep_id INT
CONSTRAINT fk_emp_dep_id FOREIGN KEY(emp_dep_id ) REFERENCES dep(dep_id)
)
十一、视图
- 视图可以看作是一张虚拟表，不存储数据，视图的本质可以看作是存储起来的SELECT语句。
- 视图中SELECT语句涉及到的表称为基表。
- 针对视图做DML操作，会影响到基表中的数据，反之亦然。
- 删除视图，不会导致基表中的数据删除。
571
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
