MySQL
控制台命令 ：
登录 ： mysql -uroot -p 回车 ，输入密码
查看可以使用的数据库 ： show databases;
使用某个数据库 : use 数据库名;
查看库中有哪些表 ： show tables;
用户管理
创建用户
create user '用户名'@'ip地址' identified by '密码';
删除用户
drop user '用户名'@'ip地址';
修改密码
set password for '用户名'@'ip地址' = Password('新密码');
权限管理
数据库名.* ：针对某个数据库中的所有表
数据库名. 表名 ： 针对某个数据库的某张表
*.* ：所有数据库的所有表
查看权限
show grants for '用户名'@'ip地址';
授权
grant 权限 on 数据库.表 to '用户名'@'ip地址';
常用的授权命令：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
允许远程登录的(本地登录服务器或者虚拟机)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
MySql的表操作
创建表
create table 表名(
字段名 类型 约束,
字段名 类型 约束
)engine = InnoDB default charset=utf8;
示例：
-- 创建表
CREATE TABLE `t_user`(
`user_id` INT(1) PRIMARY KEY AUTO_INCREMENT COMMENT '用户id,主键',
`user_name` VARCHAR(100) NOT NULL COMMENT '用户名',
`user_password` VARCHAR(100) NOT NULL COMMENT '用户密码',
`user_phone` VARCHAR(11) UNIQUE NOT NULL COMMENT '用户手机',
`user_address` VARCHAR(100) DEFAULT '南京' COMMENT '用户地址',
`user_birthday` DATE NOT NULL COMMENT '用户生日'
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- AUTO_INCREMENT 表示设置某个字段为自增(一般是主键)
-- ENGINE=INNODB 表示设置这个表的引擎是 INNODB删除表
drop table 表名;
清空表内容
delete from 表名; -- 事务的删除，可以回滚
truncate table 表名; -- 直接清空表，不能回滚
修改表
添加列： alter table 表名 add 字段 类型;
删除列 ： alter table 表名 drop column 字段;
修改列 ：
alter table 表名 modify column 字段名 新类型; -- 修改字段类型
alter table 表名 change 原字段 新字段 类型; -- 修改字段名称、类型
修改约束
添加主键 : alter table 表名 add primary key(字段);
删除主键 ： alter table 表名 drop primay key;
添加默认约束 ： alter table 表名 alter 字段 set default 值;
删除默认约束 ： alter table 表名 alter 字段 drop default 值;
添加外键 ：
alter table 从表 add constraint 外键名 foreign key 从表(字段) references 主表(主键字段)
删除外键 ： alter table 表名 drop foreign key 外键名;
练习：
创建一个订单表，订单表中，存在如下字段 ：
订单id ， 下单时间 ， 用户编号 ，订单是否付款，订单是否签收
创建的时候加上约束，最后外键指向用户表的用户id
CREATE TABLE `t_order`(
`order_id` INT(1) PRIMARY KEY AUTO_INCREMENT COMMENT '订单id,主键',
`order_time` TIMESTAMP NOT NULL COMMENT '下单时间',
`user_id` INT(1) NOT NULL COMMENT '用户编号',
`order_pay` CHAR(1) NOT NULL COMMENT '是否付款 1已付款 0未付款',
`order_wuliu` CHAR(1) COMMENT '0 未发货 1已发货 2已签收'
)ENGINE=INNODB DEFAULT CHARSET=utf8;MySQL中的存储引擎
存储引擎可以理解为表的存储结构，每种存储引擎都支持不同的特性。
-- 查看支持的存储引擎
show engines;
-- 最常用的三种引擎
InnoDB： MySQL默认的存储引擎，支持事务、支持行锁、表锁，支持各种索引、支持外键，高版本支持全文索引，但是批量插入效率低。
MyISAM：批量数据插入效率高，数据查询效率快，支持全文索引，不支持事务，不支持行锁，只支持表锁
MEMORY：使用这个存储引擎的时候，会将表中的数据加载到内存中，查询很快，对内存要求较高
MySQL的数据类型
bigint 64位大整数
整数长度 ： 整数类型声明的时候可以指定长度 ，比如 int(11)表示长度为11的int类型，长度在大多数场景下没有太大意义
小数类型： decimal 类型可以存储较大类型，计算的效率比float和double 要低
字符串类型 ： varchar 、char 、text 、 blob
varchar是可变长度，char是固定长度的，数据长度不足，用空格填充
时间日期类型： timestamp 的效率比datetime高
常用函数
数学类函数 ：
abs() 绝对值 、 mod()取余、ceil() 向上取整、floor()向下取整
字符串类函数：
length() 返回字符串长度
upper() 和 lower() 大小写函数
concat() 字符串拼接
trim() 去除空格
substring() 字符串截取
replace() 字符串替换
reverse() 字符串反转
日期函数
-- mysql日期函数
-- 当前时间
SELECT SYSDATE();
SELECT NOW();
-- 当前日期
SELECT CURDATE();
-- 当前时间
SELECT CURTIME();
-- 返回某天是星期几
SELECT DAYOFWEEK(NOW())-1;
SELECT WEEKDAY(NOW()) + 1;
-- 返回每个月的第几天
SELECT DAYOFMONTH(NOW());
-- 一年的第几天
SELECT DAYOFYEAR(NOW());
-- 返回月份
SELECT MONTH(NOW());
-- 返回某天
SELECT DAY(NOW());
-- 指定日期月份的最后一天
SELECT LAST_DAY(NOW());-- 查询哪些员工是入职当月的最后3天入职的
SELECT * FROM emp WHERE DAY(LAST_DAY(hiredate)) - DAY(hiredate) < 3;
-- 查询本周过生日的员工
SELECT * FROM emp WHERE WEEK(hiredate) = WEEK(NOW());
-- 查询下周过生日的员工
SELECT * FROM emp WHERE WEEK(hiredate) = WEEK(NOW()) + 1;
-- 查询本月过生日的员工
SELECT * FROM emp WHERE MONTH(hiredate) = MONTH(NOW());
-- 查询下月过生日的员工
SELECT * FROM emp WHERE MONTH(hiredate) = MONTH(NOW()) + 1;聚合函数
max() 最大值 、 min() 最小值 、 count() 数量 、 sum() 求和 、 avg() 平均值
使用聚合函数的时候，默认如果没有指定分组(group by) 的话，那么就把查询到的所有数据当做一个组
-- 分组函数
-- 查询工资最高和工资最低, 工资分别是多少
SELECT MAX(sal),MIN(sal) FROM emp;
-- 查询所有员工的平均工资，员工数量、奖金之和
SELECT AVG(sal) ,COUNT(1),SUM(comm) FROM emp;
-- 平均奖金,默认排除null来求平均值
SELECT AVG(comm) FROM emp;
IFNULL函数用于将NULL值替换为另一个值。它接受两个参数，如果第一个参数为NULL，则返回第二个参数，否则返回第一个参数
-- 处理空值 ifnull()
SELECT IFNULL(comm,500) FROM emp;
-- 求所有人的平均奖金，如果是null的话，按照500计算
SELECT AVG(IFNULL(comm,500)) FROM emp;
-- 求每个人工资总和，工资+奖金，如果奖金为null，按照1000计算
SELECT ename,(sal + IFNULL(comm,1000)) AS 总工资 FROM emp;
-- 求每个人工资总和，工资+奖金，在原来的基础上，每个人加500奖金
SELECT ename, (sal + IFNULL(comm,0)+500) 总工资 FROM emp;MySQL表内的操作
DML操作
增
insert into 表 (字段,字段...) values(值，值...);
insert into 表 (字段,字段...) values(值，值...),(值，值...);
insert into 表 values(值，值，值.....); -- 值的数量类型，比如和字段的数量、类型一一对应
删
delete from 表 ; -- 删除全部
delete from 表 where 条件; --根据条件删除
改
update 表 set 字段 = '值' where 条件；
练习： 往之前的user表和order表中，分别插入5条数据，然后分别操作删除命令和修改命令，完成数据的删除和修改
SQL查询语句
基本的条件查询
模糊查询 ： like 通配符： _ 或者 %
SELECT * FROM emp WHERE ename LIKE '%S%';
分页查询 ： limit关键字
-- limit分页 :limit 值1，值2
-- 值1表示从第几行开始返回数据
-- 值2表示每页显示几条数据
-- 已知 总条数 totalCount 20 、每页显示的数据条数 num 5 -- 总页数 totalPage 4
-- 点击的页数 page
-- (page-1) * num
SELECT * FROM emp LIMIT 0,5;排序 ： order by asc/desc ,默认是asc，从小到大 ， desc是从大到小
-- 排序
SELECT * FROM emp ORDER BY sal DESC;
分组 ：group by 语句
-- group by, 如果没有出现在group by后面的字段，也不能出现在select后面
-- 求每个部门的人数、平均工资
SELECT deptno,COUNT(1),AVG(sal) FROM emp GROUP BY deptno;
-- 分部门、分职业求他们的平均工资
SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno,job;
过滤分组 ： having 语句 一般用来过滤 group by之后的结果
-- 查询 哪个部门的 平均工资超过了 2000
SELECT deptno,AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal > 2000;
-- 查询哪个部门的哪个岗位的平均工资低于1000
SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno,job HAVING AVG(sal) <1000;
-- 查询哪个岗位的平均工资超过了4000
SELECT job,AVG(sal) FROM emp GROUP BY job HAVING AVG(sal) > 4000;
-- having 主要用来过滤分组之后的数据，where是分组之前的条件过滤
-- where 不可以使用聚合函数，having后面可以使用聚合函数
SELECT 5
FROM 1
WHERE 2
GROUP BY 3
HAVING 4
ORDER BY 6
多表查询
子查询
-- 子查询
-- from型 将子查询作为新表
-- where型 将子查询结果作为条件
-- exists型 判断子查询结果是否成立
-- where型
-- 使用子查询，查询员工中，工资最高的员工所在部门。
-- 先查询最高工资，再查询所在部分
SELECT MAX(sal) FROM emp;
SELECT * FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);
-- 单行子查询 子查询的结果是单行结果，将来使用单行比较运算符完成运算
-- 多行子查询 子查询的结果是多行结果，可以用in操作符
-- 通过子查询，查询所有选修刘阳老师任意课程的学生信息，(如果学生这门课有成绩，表示选修了)
-- 1，查询老师的编号
SELECT tno FROM teacher WHERE tname = '刘阳';
-- 2，通过老师编号，查到教的课程编号
SELECT cno FROM course WHERE tno =
(SELECT tno FROM teacher WHERE tname = '刘阳')
-- 3，所有课程编号中，有成绩的学生的学号
SELECT sno FROM sc WHERE cno IN
(SELECT cno FROM course WHERE tno =
(SELECT tno FROM teacher WHERE tname = '刘阳'))
AND score IS NOT NULL;
-- 4，通过学号查到学生信息
SELECT * FROM student WHERE sno IN
(SELECT sno FROM sc WHERE cno IN
(SELECT cno FROM course WHERE tno =
(SELECT tno FROM teacher WHERE tname = '刘阳'))
AND score IS NOT NULL)
-- any all
-- 查询任意一门课，超过了70分的学生的信息
SELECT DISTINCT sno FROM sc WHERE score > 70;
SELECT * FROM student WHERE sno IN (SELECT DISTINCT sno FROM sc WHERE score > 70);
-- 找出比李四任意一门成绩高的其他学生的学号
SELECT sno FROM student WHERE sname = '李四';
SELECT score FROM sc WHERE sno =
(SELECT sno FROM student WHERE sname = '李四');
-- 找比李四成绩高的
SELECT DISTINCT sno FROM sc WHERE score > ANY
(SELECT score FROM sc WHERE sno =
(SELECT sno FROM student WHERE sname = '李四'))
AND sno != (SELECT sno FROM student WHERE sname = '李四')
-- 找到比李四所有成绩都低的学生信息
/*
SELECT * FROM sc WHERE score < all
(SELECT score FROM sc WHERE sno =
(SELECT sno FROM student WHERE sname = '李四'))
AND sno != (SELECT sno FROM student WHERE sname = '李四')
*/
-- s001（85，75，60）< all（80，70） -- 不适用与比较对象有多个值的情况
-- s001(85) ,s002(65) <all (80,70) -- 适用于比较的对象只有一个值的情况
-- 找到比所有女生年龄都大的学生的信息
SELECT sage FROM student WHERE ssex = '女';
SELECT * FROM student WHERE sage > ALL
(SELECT sage FROM student WHERE ssex = '女')连接查询
-- 内连接
-- inner join on
-- 交叉连接 cross join on
-- 逗号 ,
-- 外连接
-- 左外连接left join
-- 右外连接right join
-- 全外连接 full join(mysql中不支持)
内连接和外连接的区别？
内连接查询是将多张表做笛卡尔积，根据条件将不符合条件的记录过滤掉，留下符合条件的
外连接查询的时候，需要指定一个基准表，基准表的数据会全部展示，不足的地方以null展示
左外连接以 左边的表 作为基准表，左边的表都显示
右外连接以右边的表作为基准表，右边的表全部显示
-- 自连接
-- 使用内连接查询，返回每个学生学号、姓名、课程号、分数
SELECT s1.sno,sname,cno,score FROM student s1 INNER JOIN
sc s2 ON s1.sno = s2.sno
SELECT s1.sno,sname,cno,score FROM student s1 CROSS JOIN
sc s2 ON s1.sno = s2.sno
SELECT s1.sno,sname,cno,score FROM student s1,sc s2
WHERE s1.sno = s2.sno
-- 使用外连接查询，返回每个学生学号、姓名、课程号、分数
SELECT s1.sno,sname,cno,score FROM student s1 LEFT JOIN
sc s2 ON s1.sno = s2.sno
SELECT s1.sno,sname,cno,score FROM student s1 RIGHT JOIN
sc s2 ON s1.sno = s2.sno
-- 查询所有课程成绩高于80的同学的学号、姓名；
SELECT s1.sno,sname FROM student s1 INNER JOIN sc s2
ON s1.sno = s2.sno
GROUP BY sno,sname
HAVING MIN(score) > 80;
-- 查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
SELECT s3.sno,s3.`sname` FROM sc s1,sc s2,student s3 WHERE
s1.`cno` = 'c002'
AND s2.`cno` = 'c001'
AND s1.`sno` = s2.`sno`
AND s1.`score` > s2.`score`
AND s1.`sno` = s3.`sno`
-- 查询平均成绩大于75 的所有学生的学号、姓名和平均成绩
SELECT * FROM (
SELECT s1.`sno` ,s1.sname, AVG(score) AS avg_score
FROM student s1 ,sc s2
WHERE s1.`sno` = s2.`sno`
GROUP BY s1.`sno`,s1.sname) t
WHERE avg_score > 75;
-- 查询课程名称为“J2SE”，且分数低于80 的学生姓名和分数
SELECT s1.`sname`,c1.`cname`,s2.`score` FROM student s1
INNER JOIN sc s2 ON s1.`sno` = s2.`sno`
INNER JOIN course c1 ON s2.`cno` = c1.`cno`
AND c1.`cname` = 'J2SE' AND s2.`score` < 80;
-- 查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
-- 查询老师的tno
SELECT tno FROM teacher WHERE tname = '谌燕';
SELECT cno FROM course WHERE tno =
( SELECT tno FROM teacher WHERE tname = '谌燕')
SELECT MAX(score) FROM sc WHERE cno IN
( SELECT cno FROM course WHERE tno =
( SELECT tno FROM teacher WHERE tname = '谌燕'))
-- 将最高分和 课程编号带入查询，查询到信息
SELECT sname,score FROM student s1,sc s2
WHERE s1.sno = s2.sno
AND score = (SELECT MAX(score) FROM sc WHERE cno IN
( SELECT cno FROM course WHERE tno =
( SELECT tno FROM teacher WHERE tname = '谌燕')))
AND s2.cno IN ( SELECT cno FROM course WHERE tno =
( SELECT tno FROM teacher WHERE tname = '谌燕'));
-- 查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名
SELECT s1.sno,sname FROM student s1 INNER JOIN sc s2
ON s1.sno = s2.`sno`
AND s2.`cno` = 'c001'
AND s2.`score` > 80;
-- 查询出只选修了一门课程的全部学生的学号和姓名
SELECT s1.sno,sname FROM
student s1 INNER JOIN sc s2
ON s1.sno = s2.`sno`
GROUP BY s1.sno
HAVING COUNT(1) =1;
-- 查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名
SELECT s1.sno,s1.`sname` FROM student s1 INNER JOIN sc s2 ON s1.sno = s2.`sno`
INNER JOIN sc s3 ON s2.`sno` = s3.`sno`
AND s2.`cno` = 'c001' AND s3.`cno` = 'c002'
AND s2.`score` IS NOT NULL
AND s3.`score` IS NOT NULL-- 数据库事务
-- 事务一般就是指DML操作
-- 事务特性
-- 原子性 、一致性、隔离性 、 持久性
MySQL中手动开启事务 ：start transaction;
-- 隔离级别
-- 事务并发 ： 引起
-- 脏读 ：读取到别的事务未提交的数据 (绝对不允许)
-- 虚读 ：读到别的事务提交后修改的数据(允许发生)
-- 幻读 ：读到数据的时候，两次读取的数据行数不一样(允许发生)
-- 隔离级别 解决上面的问题
-- Read Uncommited RU 读未提交
-- Read Commited RC 读已提交 Oracle
-- Repeatable Read RR 可重复读 MySQL
-- Serializable 可串行化
-- RU 出现以上三个问题
-- RC 解决 脏读
-- RR 解决 虚读 (加锁可以解决幻读)
-- Serializable 解决三个
-- 查询当前的隔离级别
SELECT @@global.tx_isolation; -- RR
SELECT @@session.tx_isolation; -- RR
SELECT @@tx_isolation;
-- 修改隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {
READ UNCOMMITTED |
READ COMMITTED |
REPEATABLE READ |
SERIALIZABLE |
}
SELECT * FROM test;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@session.tx_isolation;
扫一扫
552
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
