MySQL

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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值