MySQL的使用
数据库三层结构
- 所谓安装Mysql数据库,就是在主机安装一个数据库管理系统(DBMS),这个管理程序可以管理多个数据库.DBMS(database manage system)
- 一个数据库中可以创建多个表,以保存数据.
- 数据库管理系统(DBMS),数据库和表的关系如图所示:
数据在数据库中的存储方式
SQL语句分类
- DDL: 数据定义语句 [CREATE 表,库…]
- DML: 数据操作语句 [增加 insert,修改 update,删除 delete]
- DQL: 数据查询语句 [select]
- DCL: 数据控制语句 [管理数据库:比如用户权限 grant revoke]
创建数据库
CREATE DATABASE db01;
#默认不区分大小写
CREATE DATABASE db02 CHARACTER SET utf8
#指定区分大小写
CREATE DATABASE db03 CHARACTER SET utf8 COLLATE utf8_bin
查看,删除数据库
SHOW DATABASES
#CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET utf8 */
#/*!40100 */表示数据库版本在4以上
#在创建数据库,表的时候，为了规避关键字，可以使用反引号解决
SHOW CREATE DATABASE db01
#删除
DROP DATABASE db01
备份恢复数据库
# 备份指定数据库到指定文件路径(在DOS执行)
mysqldump -u root -p -B db02 db03 > F:\zxkworkspace\mysql\bak.sql
# 备份指定的表
# -u 用户名 -p密码 数据库 表1 表2 >文件路径
mysqldump -u -root -p db02 t1 > f:\zxkworkspace\mysql\bak1.sql
#恢复(也可以将备份的文件内容粘贴到查询中全部执行)
mysql -u root -p
source F:\zxkworkspace\mysql\bak.sql
quit#退出
创建表
#指令创建表
#注意：hsp_db02创建表时，要根据需保存的数据创建相应的列.
#id 整形 [图形化，指令]
#name 字符串
#password 字符串
#birthday 日期
CREATE TABLE `user` (
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
`birthday` DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;#或者 ENGINE MYISAM
Mysql常用数据类型(列类型)
数值型(整数)的基本使用
#使用tinyint 来演示范围 有符号 -128 ~ 127 如果没有符号 0-255
#说明： 表的字符集，校验规则, 存储引擎，老师使用默认
#1. 如果没有指定 unsinged , 则TINYINT就是有符号
#2. 如果指定 unsinged , 则TINYINT就是无符号 0-255
CREATE TABLE t3(
id TINYINT);
INSERT INTO t3 VALUES(-128);
SELECT * FROM t3
CREATE TABLE t4(
id TINYINT UNSIGNED);
INSERT INTO t4 VALUES(1);
INSERT INTO t4 VALUES(-5);
SELECT * FROM t4
数值型(bit)的使用
#演示bit类型的使用
#bit(m) m 在 1-64
#添加数据的范围按照你给的位数来确定,比如m=8表示一个字节0~255
#显示按照bit
#查询时,仍然可以按照数来查询
CREATE TABLE t05(num BIT(8));
INSERT INTO t05 VALUES(255);
SELECT * FROM t05;
SELECT * FROM t05 WHERE num=255;
数值型(小数)的基本使用
#演示decimal类型.float.double使用
#创建表
CREATE TABLE t06(
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20));
#添加数据
INSERT INTO t06 VALUES(88.12345678912345,88.12345678912345,88.12345678912345);
SELECT * FROM t06;
CREATE TABLE t07(num DECIMAL(65));
INSERT INTO t07 VALUES(8999999933338388388383838838383009338388383838383838383);
SELECT * FROM t07;
CREATE TABLE t08(num BIGINT);
INSERT INTO t08 VALUES(8999999933338388388383838838383009338388383838383838383);
SELECT * FROM t08;#Out of range value for column 'num' at row 1
字符串的基本使用
#演示字符串类型使用char varchar
#注释的快捷键 shift+ctrl+c , 注销注释 shift+ctrl+r
-- CHAR(size)
-- 固定长度字符串 最大255 字符
-- VARCHAR(size) 0~65535字节
-- 可变长度字符串 最大65532字节 【utf8编码最大21844字符 1-3个字节用于记录大小】
-- 如果表的编码是 utf8 varchar(size) size = (65535-3) / 3 = 21844
-- 如果表的编码是 gbk varchar(size) size = (65535-3) / 2 = 32766
CREATE TABLE t09(`name` CHAR(255));
CREATE TABLE t10(`name` VARCHAR(21844));
CREATE TABLE t10(`name` VARCHAR(32766)) CHARSET gbk;
DROP TABLE t10;
使用细节:
#演示字符串类型的使用细节
#char(4) 和 varchar(4) 这个4表示的是字符，而不是字节, 不区分字符是汉字还是字母
CREATE TABLE t11(`name` CHAR(4));
INSERT INTO t11 VALUES('韩好');#分配4个字符的内存
SELECT * FROM t11;
CREATE TABLE t12(`name` VARCHAR(4));
INSERT INTO t12 VALUES('韩好');#分配2个字符的内存
SELECT * FROM t12;
#如果varchar 不够用，可以考试使用mediumtext 或者longtext,
#如果想简单点，可以使用直接使用text
CREATE TABLE t13(content TEXT ,content2 MEDIUMTEXT,content3 LONGTEXT);
INSERT INTO t13 VALUES('韩顺平教育', '韩顺平教育100', '韩顺平教育1000~~');
SELECT * FROM t13;
日期类型的基本使用
CREATE TABLE t14 (
birthday DATE,
job_time DATETIME,
login_time TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
SELECT * FROM t14;
INSERT INTO t14(birthday,job_time)
VALUES('2022-11-11','2022-11-11 10:10:10');
操作表语句
创建表
CREATE TABLE `emp`(
id INT,
`name` VARCHAR(32),
sex CHAR(1),
birthday DATE,
entry_date DATE,
job VARCHAR(32),
salary DOUBLE,
resume TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
DROP TABLE `emp`;
SELECT * FROM `emp`;
INSERT INTO `emp`
VALUES(100,'小妖怪','男','2000-11-11',
'2010-11-10 11:11:11','巡山',3000,'大王叫我来巡山');
修改表
提示:ALTER语句为修改表结构的语句,不直接操作某一行的记录
#修改表
#在rusume后面增加一个列
ALTER TABLE `emp`
ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER RESUME
DESC emp -- 显示表结构,可以查看表的所有列
#修改表
#修改job列,使其长度为60
ALTER TABLE emp
MODIFY job VARCHAR(60) NOT NULL DEFAULT ''-- 默认值不许为空,默认是''字符
#删除列
ALTER TABLE emp
DROP sex
#修改表名
RENAME TABLE emp TO employee
#修改字符集
ALTER TABLE employee CHARACTER SET utf8
#修改列名
ALTER TABLE employee
CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT ''
DESC employee
数据库 CRUD语句
C[create]R[read]U[update]D[delete]语句对应:
- Insert语句 (添加数据)
- Update语句 (更新数据)
- Delete语句 (删除数据)
- Select语句 (查找数据)
Insert语句
使用 INSERT 语句向表中插入数据
#练习INSERT语句
CREATE TABLE `goods`(id INT,goods_name VARCHAR(10),price DOUBLE);
-- 添加数据
INSERT INTO `goods`(id,goods_name,price)
VALUES(10,'华为手机',2000);
SELECT * FROM goods;
#练习
DESC employee;
INSERT INTO employee VALUES(200,'大王','1999-10-10','2000-11-11 11:11:11','管理',10000000,'代我去巡山','鍪啊');
SELECT * FROM employee;
细节说明:
#说明insert 语句的细节
-- 1.插入的数据应与字段的数据类型相同。
-- 比如 把 'abc' 添加到 int 类型会错误
DESC goods;
SELECT * FROM goods;
INSERT INTO goods (id,goods_name,price)
VALUES('韩顺平','小米手机',2000);-- wrong
-- 2. 数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
INSERT INTO goods (id,goods_name,price)
VALUES('韩顺平','小米手机小米手机小米手机小米手机',2000);-- wrong
-- 3. 在values中列出的数据位置必须与被加入的列的排列位置相对应。
INSERT INTO goods(id,goods_name,price)
VALUES('vivo手机',40,3000);-- wrong
INSERT INTO goods(goods_name,id,price)
VALUES('vivo手机',40,3000);-- true
-- 4. 字符和日期型数据应包含在单引号中。
INSERT INTO goods(id,goods_name,price)
VALUES(40,vivo手机,3000);-- wrong
INSERT INTO goods(id,goods_name,price)
VALUES('50','vivo手机',3000);-- true:int型用字符串会自动转int型
-- 5. 列可以插入空值[前提是该字段允许为空]，insert into table value(null)
INSERT INTO goods(id,goods_name,price)
VALUES(40,'vovo手机',null);
-- 6. insert into tab_name (列名..) values (),(),() 形式添加多条记录
INSERT INTO `goods` (id,goods_name,price)
VALUES(50,'三星手机',2000),(60,'海尔手机',3000);
-- 7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称
INSERT INTO goods VALUES(70,'IBM手机',5000);-- 省略写法不能使用默认值机制
-- 8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错
-- 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给null
-- 如果我们希望指定某个列的默认值，可以在创建表时指定
-- 修改列默认值
ALTER TABLE goods
MODIFY price DOUBLE NULL DEFAULT 500;
INSERT INTO goods(id,goods_name)
VALUES(80,'格力手机');
Update语句
使用 update 语句修改表中数据
#演示update语句
#修改表中的记录没带where 修改所有记录
SELECT * FROM employee;
UPDATE employee SET salary =5000;
UPDATE employee SET salary =3000 WHERE user_name='小妖怪';
UPDATE employee SET salary =salary+1000 WHERE user_name='大王';
#可以修改多个列
UPDATE employee SET salary=salary+1000,job='出主意'
WHERE user_name='大王';
细节说明:
Delete语句
使用 delete 语句删除表中数据
-- delete语句没有where会删除所有内容
DELETE FROM employee
WHERE user_name='大王';
INSERT INTO employee VALUES(200,'大王','1999-10-10','2000-11-11 11:11:11','管理',10000000,'代我去巡山','鍪啊');
SELECT * FROM employee;
-- 删表所有数据
DELETE FROM employee;
-- 删这个表
DROP TABLE employee;
细节说明:
Select语句
-- ****创建新的表(student)********
create table student(
id int not null default 1,
name varchar(20) not null default '',
chinese float not null default 0.0,
english float not null default 0.0,
math float not null default 0.0
);
insert into student(id,name,chinese,english,math) values(1,'韩顺平',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'张飞',67,98,56);
insert into student(id,name,chinese,english,math) values(3,'宋江',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'关羽',88,98,90);
insert into student(id,name,chinese,english,math) values(5,'赵云',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'欧阳锋',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);
DELETE FROM student
WHERE id=5;
-- 查询表中所有学生的信息。
SELECT * FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;
-- 过滤表中重复数据 distinct 。
SELECT DISTINCT * FROM student;
-- 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT math FROM student;
使用表达式和AS语句
-- select 语句的使用
SELECT * FROM student;
-- 统计每个学生总分
SELECT `name`,(chinese+english+math) FROM student;
-- 在所有总分上加10分
SELECT `name`,(chinese+english+math+10) FROM student;
-- 给总分取别名
SELECT `name` AS '名字',(chinese+english+math+10) AS total_score FROM student;
where过滤查询
在 where 子句中经常使用的运算符
练习:
#使用WHERE子句进行过滤查询
SELECT * FROM student;
SELECT `name`,(chinese+math+english) `sum`
FROM student;-- as可以省略
SELECT * FROM student WHERE `name`='赵云';
SELECT * FROM student WHERE english>90;
SELECT * FROM student
WHERE (chinese+math+english)>200;
-- exercise
-- 查询math大于60 并且(and) id大于4的学生成绩
SELECT * FROM student WHERE math>60 AND id>4;
-- 查询英语成绩大于语文成绩的同学
SELECT * FROM student WHERE english>chinese;
-- 查询总分大于200分 并且 数学成绩小于语文成绩,的姓赵的学生.
-- 赵% 表示 名字以韩开头的就可以
SELECT * FROM student
WHERE (chinese+math+english)>200 AND math<chinese AND `name` LIKE '赵%';
-- 查询英语分数在 80－90之间的同学。
SELECT * FROM student WHERE english BETWEEN 80 AND 90;
-- between .. and .. 是 闭区间
-- 查询数学分数为89,90,91的同学。
SELECT * FROM student WHERE math=89 OR math=90 OR math=91;
SELECT * FROM student WHERE math IN (89,90,91);
-- 查询所有姓李的学生成绩。
SELECT * FROM student WHERE `name` LIKE '赵%';
-- 查询数学分>80，语文分>80的同学
order by 子句
SELECT * FROM student;
INSERT INTO student VALUES(8,'韩琛',45,65,99);
-- 演示order by使用
-- 对数学成绩升序排序
SELECT * FROM student ORDER BY math;
SELECT * FROM student ORDER BY math DESC;
-- 对总分降序输出
SELECT `name`,(chinese+math+english) total_score FROM student ORDER BY (chinese+math+english) DESC;
-- 对姓李的学生成绩升序输出
SELECT *, (chinese+math+english) total_score FROM student WHERE `name` LIKE '韩%' ORDER BY total_score;
函数
合计/聚合/统计函数
-- 演示mysql中统计函数(聚合函数)的使用
-- 统计一个班中的学生
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于90 的学生
SELECT COUNT(*) FROM student WHERE math>90;
-- 统计总数大于250的人数
SELECT COUNT(*) FROM student WHERE (chinese+math+english>250);
-- count(*) 和count(列)的区别
CREATE TABLE t15(
`name` VARCHAR(20));
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);
SELECT COUNT(*) FROM t15;-- 返回总行数4
SELECT COUNT(`name`) FROM t15;-- 返回该字段不为null的行数3
-- 演示sum函数的使用
-- 统计一个班数学总成绩
SELECT SUM(math) FROM student;
-- 各科总成绩
SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
-- 各科成绩总和
SELECT SUM(chinese+math+english) FROM student;
-- 语文成绩平均分
SELECT SUM(chinese)/COUNT(*) FROM student;
-- 演示avg的使用
SELECT AVG(chinese) FROM student;
-- 总分平均分
SELECT AVG(chinese+math+english) FROM student;
-- 演示max和min的使用
SELECT MAX(chinese+math+english),MIN(chinese+math+english) FROM student;
配合 group by ,having 子句使用
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');
#创建表EMP雇员
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,-- 工资级别
losal DECIMAL(17,2) NOT NULL,-- 该级别最低工资
hisal DECIMAL(17,2) NOT NULL-- 该级别最高工资
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
SELECT * FROM dept;
SELECT * FROM emp;
SELECT * FROM salgrade;
#演示group by+having
-- 每个部门的平均工资和最高工资
SELECT AVG(sal),MAX(sal),deptno FROM emp GROUP BY deptno;
SELECT sal FROM emp GROUP BY deptno;
-- 每个部门的每种岗位的平均工资和最低工资
-- ?显示每个部门的每种岗位的平均工资和最低工资
-- 老师分析 1. 显示每个部门的平均工资和最低工资
-- 2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal),MIN(sal),deptno,job FROM emp GROUP BY deptno,job;
-- 显示平均工资低于2000的部门号和它的平均工资
SELECT deptno,AVG(sal) AS `avg` FROM emp GROUP BY deptno HAVING `avg` <=2000 ;
字符串相关函数
-- 演示字符串相关函数
SELECT * FROM emp;
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(`ename`) FROM emp;
-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename,'的工作是',job) FROM emp;
-- INSTR (string ,substring ) 返回substring在string中出现的位置,没有返回0
-- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('zxkedu','edu') FROM DUAL;
-- UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp;
-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp;
-- LEFT (string2 ,length ) 从string2中的左边起取length个字符
-- RIGHT (string2 ,length ) 从string2中的右边起取length个字符
SELECT LEFT(ename,2) FROM emp;
SELECT RIGHT(ename,2) FROM emp;
-- LENGTH (string ) string长度[按照字节]
SELECT LENGTH(ename) FROM emp;
-- REPLACE (str ,search_str ,replace_str )
-- 在str中用replace_str替换search_str
-- 如果是manager 就替换成 经理(区分大小写)
SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp;
-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('zk','zxk') FROM DUAL;
-- SUBSTRING (str , position [,length ])
-- 从str的position开始【从1开始计算】,取length个字符
-- 从ename 列的第一个位置开始取出2个字符
SELECT SUBSTRING(ename,2) FROM emp;
-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM(' zxk') FROM DUAL;
SELECT RTRIM('zxk ') FROM DUAL;
SELECT TRIM(' zxk ') FROM DUAL;
-- 练习: 以首字母小写的方式显示所有员工emp表的姓名
-- 方法1
-- 思路先取出ename 的第一个字符，转成小写的
-- 把他和后面的字符串进行拼接输出即可
-- method01
SELECT CONCAT(LCASE(LEFT(ename,1) ),SUBSTRING(ename,2) ) newname1 FROM emp;
-- method02
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1) ),SUBSTRING(ename,2) ) newname2 FROM emp;
数学相关函数
-- 演示数学相关函数
-- ABS(num) 绝对值
SELECT ABS(-15) FROM DUAL;
-- BIN (decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL;
-- CEILING (number2 ) 向上取整, 得到比num2 大的最小整数
SELECT CEILING(1.1) FROM DUAL;
-- CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的8, 转成 2进制输出
SELECT CONV(16,16,10) FROM DUAL;
-- 下面的含义是 8 是16进制的8, 转成 2进制输出
-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;
-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL;
-- HEX (DecimalNumber ) 转十六进制
SELECT CONV(32,10,16) FROM DUAL;
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1,-10,4) FROM DUAL;
-- MOD (numerator ,denominator ) 求余
SELECT MOD(10,3) FROM DUAL;
-- RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 老韩说明
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果seed不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT RAND(3) FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;
时间日期相关函数
-- 日期时间相关函数
-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME ( ) 当前时间
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- 创建测试表 信息表
CREATE TABLE mes(
id INT ,
content VARCHAR(30),
send_time DATETIME);
-- 添加一条记录
INSERT INTO mes
VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());
UPDATE mes SET send_time=NOW() WHERE id=2;-- update
SELECT * FROM mes ORDER BY send_time;
SELECT * FROM mes;
SELECT NOW() FROM DUAL;
-- 上应用实例
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间.
SELECT id,content,DATE(send_time) FROM mes;
-- 请查询在10分钟内发布的新闻, 思路一定要梳理一下.
SELECT * FROM mes WHERE send_time>DATE_SUB(NOW(),INTERVAL 10 MINUTE);
SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE)>NOW();
-- 请在mysql 的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL;
-- 请用mysql 的sql语句求出你活了多少天? [练习] 1986-11-11 出生
SELECT DATEDIFF(CURRENT_DATE,'1998-07-29') FROM DUAL;
-- 如果你能活80岁，求出你还能活多少天.[练习] 1986-11-11 出生
-- 先求出活80岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 1986-11-11->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒
-- '1986-11-11' 可以date,datetime timestamp
SELECT DATE_ADD('1998-07-29',INTERVAL 80 YEAR) FROM DUAL;
SELECT DATEDIFF('2078-07-29',NOW()) FROM DUAL;
SELECT DATEDIFF(DATE_ADD('1998-07-29',INTERVAL 80 YEAR),NOW()) FROM DUAL;
SELECT TIMEDIFF('10:11:11','04:10:10') FROM DUAL;
-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
-- unix_timestamp() : 返回的是1970-1-1 到现在的秒数
SELECT unix_timestamp() FROM DUAL;
-- FROM_UNIXTIME() : 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
--
SELECT FROM_UNIXTIME(1644769696,'%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1644769696,'%Y-%m-%d %H-%i-%s') FROM DUAL;
SELECT FROM_UNIXTIME(unix_timestamp()) FROM DUAL;
SELECT * FROM mysql.user \G
加密和系统函数
-- 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL;-- root@localhost
-- DATABASE() 查询当前使用数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32的字符串，常用(用户密码)加密
-- root 密码是 hsp -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('root') FROM DUAL;
SELECT LENGTH(MD5('root')) FROM DUAL;
-- 演示用户表，存放密码时，是md5
CREATE TABLE hsp_user
(id INT ,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user
VALUES(100, '韩顺平', MD5('hsp'));
SELECT * FROM hsp_user; -- csdn
SELECT * FROM hsp_user -- SQL注入问题
WHERE `name`='韩顺平' AND pwd = MD5('hsp');
-- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD函数加密
SELECT PASSWORD('root') FROM DUAL;-- 数据库的 *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
-- select * from mysql.user \G 从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user;
流程控制函数
SELECT * FROM emp;
# 演示流程控制语句
# IF(expr1,expr2,expr3) 如果expr1为True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2) 如果expr1不为空NULL,则返回expr1,否则返回expr2
SELECT IFNULL( NULL, '韩顺平教育') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果expr1 为TRUE,则返回expr2,如果expr2 为t, 返回 expr4, 否则返回 expr5
#谁先对输出谁
SELECT CASE
WHEN TRUE THEN 'jack' -- jack
WHEN FALSE THEN 'tom'
ELSE 'mary' END
-- 1. 查询emp 表, 如果 comm 是null , 则显示0.0
-- 老师说明，判断是否为null 要使用 is null, 判断不为空 使用 is not
SELECT ename,job,IF(comm IS NULL,0.0,comm) FROM emp;
SELECT ename,job,IFNULL(comm,0.0) FROM emp;
SELECT ename,job,CASE
WHEN comm IS NULL THEN 0.0
ELSE comm END
FROM emp;
-- 2. 如果emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename,(SELECT CASE -- 靠近CASE的SELECT可以省略
WHEN job ='CLERK' THEN '职员'
WHEN job ='MANAGER' THEN '经理'
WHEN job ='SALESMAN' THEN '销售人员'
ELSE job END)AS 'job'
FROM emp;
mysql表查询–加强
练习:
-- 查询加强
-- ■ 使用where子句
-- ?如何查找1992.1.1后入职的员工
-- 老师说明： 在mysql中,日期类型可以直接比较, 需要注意格式
DESC emp;
SELECT * FROM emp WHERE hiredate>'1992-01-01';
-- ■ 如何使用like操作符(模糊)
-- %: 表示0到多个任意字符 _: 表示单个任意字符
-- ?如何显示首字符为S的员工姓名和工资
SELECT ename,sal FROM emp WHERE ename LIKE 's%';
-- ?如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename,sal FROM emp WHERE ename LIKE '__O%';
-- ■ 如何显示没有上级的雇员的情况
SELECT * FROM emp WHERE mgr IS NULL;
-- ■ 查询表结构
DESC emp;
-- 使用order by子句
-- ?如何按照工资的从低到高的顺序[升序]，显示雇员的信息
SELECT * FROM emp ORDER BY sal;
-- ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp ORDER BY deptno ASC ,sal DESC;
SELECT * FROM emp GROUP BY deptno ,sal ;
分页查询
-- 分页查询
-- 按雇员的id号升序取出， 每页显示3条记录，请分别显示 第1页，第2页，第3页
-- 第1页
SELECT * FROM emp
ORDER BY empno
LIMIT 0, 3;
-- 第2页
SELECT * FROM emp
ORDER BY empno
LIMIT 3, 3;
-- 第3页
SELECT * FROM emp
ORDER BY empno
LIMIT 6, 3;
SELECT * FROM emp;
SELECT * FROM emp ORDER BY empno DESC LIMIT 10,5;
-- 推导一个公式
SELECT * FROM emp
ORDER BY empno
LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数
分组函数和分组子句 group by
-- 增强group by 的使用
SELECT * FROM emp;
-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*),AVG(sal) ,job FROM emp GROUP BY job;
-- (2) 显示雇员总数，以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非null, 就是count(列)，如果该列的值为null, 是
-- 不会统计 , SQL 非常灵活，需要我们动脑筋.
SELECT COUNT(*),COUNT(comm) FROM emp;
-- 老师的扩展要求：统计没有获得补助的雇员数
SELECT COUNT(IF(comm IS NULL,1,NULL)) FROM emp;
SELECT COUNT(*)-COUNT(comm) FROM emp;
-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr) FROM emp;
-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal)-MIN(sal) FROM emp;
-- 应用案例：请统计各个部门group by 的平均工资 avg，
-- 并且是大于1000的 having，并且按照平均工资从高到低排序， order by
-- 取出前两行记录 limit 0, 2
SELECT AVG(sal) sal_avg FROM emp GROUP BY deptno HAVING sal_avg>1000 ORDER BY sal_avg DESC LIMIT 0,2;
数据分组等的总结
-
GROUP BY的使用顺序:WHERE 比较运算/逻辑运算=0/1 GROUP BY 字段1,字段2 HAVING 比较运算/逻辑运算
分组前过滤+分组+分组后过滤
按每个字段1中的字段2查询:GROUP BY 字段1,字段2注意:数据分组的正确语法顺序:WHERE+GROUP BY+HAVING+ORDER BY+LIMIT
-
ORDER BY的排序顺序同GROUP BY,按字段1的升序基础上字段2降序查询:ORDER BY 字段1 ASC,字段2 DESC
-
GROUP BY 和ORDER BY的分组区别:
从结果上看:分组结果会将 分组后 同组且不再分 的行聚合为一行,
排序结果 分组后 则不会 -
日期时间类:
-
'1991-01-02’省略了01的0的日期字符串间比较会出错:
‘1991-10-10’>‘1991-2-10’=0假,按日期类型比较应为真 -
DATE类日期可以和字符串类日期比较
多表查询
SELECT * FROM dept;
SELECT * FROM salgrade;
SELECT * FROM emp;
-- 多表查询
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*
老韩分析
1. 雇员名,雇员工资 来自 emp表
2. 部门的名字 来自 dept表
3. 需求对 emp 和 dept查询 ename,sal,dname,deptno
4. 当我们需要指定显示某个表的列是，需要 表.列名
*/
SELECT ename ,sal,dname,emp.deptno FROM emp , dept WHERE emp.deptno=dept.deptno;
-- 老韩小技巧：多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为10的部门名、员工名和工资
SELECT ename ,sal,dname,emp.deptno FROM emp , dept WHERE emp.deptno=dept.deptno AND emp.deptno=10;
-- ?显示各个员工的姓名，工资，及其工资的级别
-- 思路 姓名，工资 来自 emp 13
-- 工资级别 salgrade 5
-- 写sql , 先写一个简单，然后加入过滤条件...
SELECT ename,sal,grade FROM emp,salgrade WHERE sal BETWEEN losal AND hisal;
自连接
-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 老韩分析： 员工名字 在emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp表的 mgr 列关联
-- 这里老师小结：
-- 自连接的特点 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确，可以指定列的别名 列名 as 列的别名
SELECT * FROM emp;
SELECT emp.ename emp,boss.ename boss FROM emp,emp AS boss WHERE emp.mgr=boss.empno;
子查询(嵌套查询)
单行子查询
单行子查询是指只返回一行数据的子查询语句
多行子查询(in)
多行子查询指返回多行数据的子查询 使用关键字 in
-- 子查询的演示
-- 请思考：如何显示与SMITH同一部门的所有员工?
/*
1. 先查询到 SMITH的部门号得到
2. 把上面的select 语句当做一个子查询来使用
*/
SELECT deptno FROM emp WHERE ename='SMITH';
SELECT *
FROM emp
WHERE deptno=(
SELECT deptno
FROM emp
WHERE ename='SMITH'
);
-- 课堂练习:如何查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号, 但是不含10号部门自己的雇员.
/*
1. 查询到10号部门有哪些工作
2. 把上面查询的结果当做子查询使用
*/
SELECT DISTINCT job
FROM emp
WHERE deptno =10;
SELECT ename,job,sal,deptno
FROM emp
WHERE job in(
SELECT DISTINCT job
FROM emp
WHERE deptno =10
) AND deptno <>10;
-- 查询ecshop中各个类别中，价格最高的商品
-- 查询 商品表
-- 先得到 各个类别中，价格最高的商品 max + group by cat_id, 当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询
-- 未过滤的表
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods;
-- 临时表
SELECT cat_id,MAX(shop_price) AS hi_price
FROM ecs_goods
GROUP BY cat_id;
-- 答案
SELECT goods_id,ecs_goods.cat_id,goods_name,shop_price
FROM(
SELECT cat_id,MAX(shop_price) AS hi_price
FROM ecs_goods
GROUP BY cat_id
) temp,ecs_goods
WHERE temp.cat_id=ecs_goods.cat_id
AND hi_price=shop_price;
all&any
-- all 和 any的使用
-- 请思考:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename,sal,deptno
FROM emp
WHERE sal>ALL(
SELECT sal FROM emp WHERE deptno=30
);
-- method
SELECT ename,sal,deptno
FROM emp
WHERE sal>(
SELECT MAX(sal) FROM emp WHERE deptno=30
);
-- 请思考:如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename,sal,deptno
FROM emp
WHERE sal>ANY(
SELECT sal FROM emp WHERE deptno=30
);
-- method
SELECT ename,sal,deptno
FROM emp
WHERE sal>(
SELECT MIN(sal) FROM emp WHERE deptno=30
);
多列子查询
-- 多列子查询
-- 查询与Allan的部门和岗位完全相同的所有雇员(不含本人)
SELECT * FROM emp;
SELECT deptno ,job FROM emp WHERE ename='ALLEN';
-- 分析: 2 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT *
FROM emp
WHERE (deptno,job) =(
SELECT deptno ,job
FROM emp
WHERE ename='ALLEN'
)AND ename <>'ALLEN';
-- 请查询 和宋江数学，英语，语文
-- 成绩 完全相同的学生
SELECT * FROM student;
SELECT chinese,math,english FROM student WHERE `name` ='宋江';
SELECT *
FROM student
WHERE (chinese,math,english)=(
SELECT chinese,math,english
FROM student
WHERE `name` ='宋江'
)AND `name` <>'宋江';
from 子句中使用子查询
-- 子查询练习
-- 查询每个部门工资高于本部门平均工资的人
-- 子查询
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
-- 多表查询
SELECT ename,sal,sal_avg,emp.deptno
FROM emp,(
SELECT deptno,AVG(sal) AS sal_avg
FROM emp
GROUP BY deptno
) temp
WHERE emp.deptno=temp.deptno
AND sal>sal_avg;
-- 查询每个部门工资最高人的详细信息
-- 子查询:部门最高工资
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;
-- 1)多列子查询
SELECT * FROM emp WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM emp GROUP BY deptno);
-- 2)多表查询
SELECT ename,sal,sal_max,emp.deptno
FROM emp,(
SELECT deptno,MAX(sal) AS sal_max
FROM emp
GROUP BY deptno
) temp
WHERE emp.deptno=temp.deptno
AND sal=sal_max;
-- 查每个部门的信息:部门名,编号,地址,人数
SELECT * FROM dept;
-- 子查询
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
-- 多表查询
SELECT dept.*,`count`-- dname,dept.deptno,loc,`count`
FROM dept,(
SELECT deptno,COUNT(*) `count`
FROM emp
GROUP BY deptno
) temp
WHERE dept.deptno=temp.deptno;
表复制
自我复制数据(蠕虫复制)
-- 表的复制
-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
-- 创建表
CREATE TABLE mytab_01
(id INT ,
`name` VARCHAR(32),
sal DOUBLE ,
job VARCHAR(32),
deptno INT );
DESC mytab_01;
SELECT * FROM mytab_01;
-- 从emp表复制字段值
SELECT * FROM emp;
INSERT INTO mytab_01
(id,`name`,sal,job,deptno)
SELECT empno,ename,sal,job,deptno FROM emp;
-- 自我复制
INSERT INTO mytab_01
SELECT * FROM mytab_01;
SELECT COUNT(*) FROM mytab_01;
-- 删除重复记录5step
-- 1记录不重复的子查询
SELECT DISTINCT * FROM mytab_01;
-- 2复制不重复记录到新表()
CREATE TABLE tmp like mytab_01;-- 复制表结构到新表
SELECT * FROM tmp;
INSERT INTO tmp
SELECT DISTINCT * FROM mytab_01;
-- 3删除数据
DELETE FROM mytab_01;
-- 4复制不重复记录到旧表
INSERT INTO mytab_01
SELECT * FROM tmp;
-- 5删除临时表
DROP TABLE tmp;
合并查询
-- 合并查询
SELECT ename,sal,job FROM emp WHERE sal>2500; -- 5
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union all 就是将两个查询结果合并，不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union 就是将两个查询结果合并，会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
外连接
SELECT * FROM dept;
-- 外连接
-- 比如：列出部门名称和这些部门的员工名称和工作，
-- 同时要求 显示出那些没有员工的部门。
SELECT dname,ename,job,dept.deptno
FROM emp , dept
WHERE emp.deptno=dept.deptno
ORDER BY deptno;
-- 列出部门名称和这些部门的员工信息(名字和工作)，
-- 同时列出那些没有员工的部门名。5min
-- 使用右外连接实现
SELECT dname,ename,job,dept.deptno
FROM emp RIGHT JOIN dept
ON emp.deptno=dept.deptno
ORDER BY deptno;
-- 左外连接
SELECT dname,ename,job,dept.deptno
FROM dept LEFT JOIN emp
ON emp.deptno=dept.deptno
ORDER BY deptno;
约束
主键
-- 主键使用
-- id name email
CREATE TABLE t17
(id INT PRIMARY KEY, -- 表示id列是主键
`name` VARCHAR(32),
email VARCHAR(32));
-- 主键列的值是不可以重复
INSERT INTO t17
VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17
VALUES(2,'mary','mary@sohu.com');
INSERT INTO t17
VALUES(1,'zxk','zxk@sohu.com');
SELECT * FROM t17;
-- 主键使用的细节讨论
-- primary key不能重复而且不能为 null。
INSERT INTO t17
VALUES(NULL, 'hsp', 'hsp@sohu.com');
-- 一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)
CREATE TABLE t18
(id INT PRIMARY KEY, -- 表示id列是主键
`name` VARCHAR(32), PRIMARY KEY -- 错误的
email VARCHAR(32));
-- 演示复合主键 (id 和 name 做成复合主键)
CREATE TABLE t18
(id INT ,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id,`name`) -- 这里就是复合主键
);
INSERT INTO t18
VALUES(1, 'tom', 'tom@sohu.com');
INSERT INTO t18
VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t18
VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键
SELECT * FROM t18;
-- 主键的指定方式 有两种
-- 1. 直接在字段名后指定：字段名 primakry key
-- 2. 在表定义最后写 primary key(列名);
CREATE TABLE t19
(id INT ,
`name` VARCHAR(32) PRIMARY KEY,
email VARCHAR(32)
);
CREATE TABLE t20
(id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(`name`)
);
-- 使用desc 表名，可以看到primary key的情况
DESC t20 -- 查看 t20表的结果，显示约束的情况
DESC t18-- 复合主键
非空
唯一
-- unique的使用
CREATE TABLE t21
(id INT UNIQUE,
`name` VARCHAR(32),
emain VARCHAR(32)
);
INSERT INTO t21
VALUES(1,'jack','jack@sohu.com');
INSERT INTO t21 -- id 列重复
VALUES(1,'mary','mary@sohu.com');
-- 使用细节
-- 1. 如果没有指定 not null , 则 unique 字段可以有多个null
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
INSERT INTO t21
VALUES(NULL,'hsp','hsp@sohu.com');
SELECT * FROM t21;
-- 2. 一张表可以有多个unique字段
CREATE TABLE t22
(id INT UNIQUE NOT NULL,
`name` VARCHAR(32) UNIQUE,
email VARCHAR(32)
);
DESC t22;-- id约束为主键
外键
-- 创建主表
CREATE TABLE my_class
(id INT UNIQUE,-- 班级编号
nam VARCHAR(32) NOT NULL DEFAULT ''
);
INSERT INTO my_class
VALUES(100,'java'),(200,'web');
DESC my_class;
SELECT * FROM my_class;
-- 创建从表
CREATE TABLE my_stu
(id INT ,-- 学生编号
`name` VARCHAR(32),
class_id INT,-- 学生所在班级的编号
FOREIGN KEY (class_id) REFERENCES my_class(id));-- 定义外键约束
INSERT INTO my_stu
VALUES(1,'tom',100);
INSERT INTO my_stu
VALUES(2,'jack',200);
INSERT INTO my_stu
VALUES(1,'tom',100);
INSERT INTO my_stu
VALUES(4,'mary',300);-- 这里会失败...因为300班级不存在
INSERT INTO my_stu
VALUES(5,'zxk',NULL);-- 同unique原理:NULL不确定,所以主表字段值可以是多个NULL
SELECT * FROM my_stu;
-- 删除:一旦建立主外键关系,主表的记录就不能随意删除
-- 除非没有从表的记录使用该外键字段:即要先删除从表中使用该外键的记录才能再删除主表的该外键的记录
DELETE FROM my_class WHERE id=100;-- wrong
check
-- 演示check的使用
-- mysql5.7目前还不支持check ,只做语法校验，但不会生效
-- 了解
-- 学习 oracle, sql server, 这两个数据库是真的生效.
-- 测试
CREATE TABLE t23(
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex CHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK (sal>1000 AND sal<2000)
);
-- 添加数据
INSERT INTO t23
VALUES(1,'hsp','mid',1);
SELECT * FROM t23;
练习
-- 商品信息
CREATE TABLE goods
(goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (unitprice>=1.0 AND unitprice<=9999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(64) NOT NULL DEFAULT ''
);
-- 客户信息
CREATE TABLE customer
(customer_id CHAR(8) PRIMARY KEY,
`name` VARCHAR(10) NOT NULL DEFAULT '',
address VARCHAR(64) NOT NULL DEFAULT '',
email VARCHAR(64) UNIQUE NOT NULL DEFAULT '',
sex ENUM('男','女') NOT NULL,
card_Id CHAR(18)
);
-- 订单信息
CREATE TABLE purchase
(order_id INT UNSIGNED PRIMARY KEY,
customer_id CHAR(8) NOT NULL DEFAULT '',-- 外键约束
goods_id INT NOT NULL DEFAULT 0,-- 外键约束
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);
自增长
-- 自增长
CREATE TABLE t24
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
DESC t24;
INSERT INTO t24(id,email,`name`)
VALUES(NULL,'hsp.com','hsp');
INSERT INTO t24(email,`name`)
VALUES('sohu.com','sohu');
INSERT INTO t24
VALUES(NULL,'zxk.com','zxk');
SELECT * FROM t24;
-- 修改默认的自增长开始值1
INSERT INTO t24
VALUES(666,'zxk.com','zxk');
INSERT INTO t24(email,`name`)
VALUES('sohu.com','sohu');
-- 修改默认的自增长开始值2
CREATE TABLE t25
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
ALTER TABLE t25 AUTO_INCREMENT=100;
INSERT INTO t25(id,email,`name`)
VALUES(NULL,'hsp.com','hsp');
SELECT * FROM t25;
索引
入门
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;
#创建表EMP雇员
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;
#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
);
#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
delimiter $$
#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#定义了一个变量 chars_str， 类型 varchar(100)
#默认给 chars_str 初始值 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
# concat 函数 : 连接函数mysql函数
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
#这里我们又自定了一个函数,返回一个随机的部门号
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
#创建一个存储过程， 可以添加雇员
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
#autocommit = 0 含义: 不要自动提交
set autocommit = 0; #默认不提交sql语句
repeat
set i = i + 1;
#通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
#commit整体提交所有sql语句，提高效率
commit;
end $$
#添加8000000数据
call insert_emp(100001,8000000)$$
#命令结束符，再重新设置为;
delimiter ;
SELECT COUNT(*) FROM emp;
SELECT * FROM emp WHERE empno=1234567;
-- 使用索引来优化一下， 体验索引的牛
-- 在没有创建索引前 , emp.ibd 文件大小 是 524m
-- 创建索引后 emp.ibd 文件大小 是 655m [索引本身也会占用空间.]
-- 创建ename列索引,emp.ibd 文件大小 是 827m
-- empno_index 索引名称
-- ON emp (empno) : 表示在 emp表的 empno列创建索引
CREATE INDEX empno_index ON emp(empno);
-- 创建索引后，只对创建了索引的列有效
SELECT * FROM emp WHERE ename='PjDlwy';
CREATE INDEX ename_index ON emp(ename);
原理
类型
使用
-- 演示mysql的索引的使用
-- 创建索引
CREATE TABLE t25
(id INT,
ic INT,
`name` VARCHAR(32));
DESC t25;
SHOW INDEXES FROM t25;
-- 添加唯一索引
CREATE UNIQUE INDEX ic_index ON t25(ic);
--
ALTER TABLE t25
ADD UNIQUE INDEX (ic);
-- 添加普通索引
CREATE INDEX name_index ON t25(`name`);
--
ALTER TABLE t25
ADD INDEX (`name`);
-- 添加主键索引
ALTER TABLE t25
ADD PRIMARY KEY (id);
-- 删除索引
DROP INDEX ic_index ON t25;
ALTER TABLE t25 DROP INDEX name_index;
ALTER TABLE t25 DROP PRIMARY KEY;
-- 查询索引
SHOW INDEX FROM t25;
SHOW INDEXES FROM t25;
SHOW KEY FROM t25;
DESC t25;
使用小结
事务
定义
-- 事务的几个重要概念和具体操作
-- 创建测试表
CREATE TABLE t27
(id INT ,
`name` VARCHAR(32));
-- 开始事务
START TRANSACTION;
-- 3. 设置保存点
SAVEPOINT a;
-- 执行dml 操作
INSERT INTO t27
VALUES(100,'jack');
-- 3. 设置保存点
SAVEPOINT b;
-- 执行dml 操作
INSERT INTO t27
VALUES(200,'mary');
SELECT * FROM t27;
-- 回退到 b
ROLLBACK TO b;
-- 继续回退 a
ROLLBACK TO a;
-- 如果这样, 表示直接回退到事务开始的状态.
ROLLBACK;
-- 提交
COMMIT;
回退事务
提交事务
细节
-- 讨论 事务细节
-- 1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
SELECT * FROM t27;
INSERT INTO t27
VALUES(300,'zxk');
-- 2. 如果开始一个事务，你没有创建保存点. 你可以执行 rollback，
-- 默认就是回退到你事务开始的状态
START TRANSACTION;
INSERT INTO t27
VALUES(400,'smith'),(500,'hsp');
ROLLBACK;-- 表示直接回退到事务开始的的状态
COMMIT;
-- 3. 你也可以在这个事务中(还没有提交时), 创建多个保存点.比如: savepoint aaa;
-- 执行 dml , savepoint bbb
-- 4. 你可以在事务没有提交前，选择回退到哪个保存点
-- 5. InnoDB 存储引擎支持事务 , MyISAM 不支持
-- 6. 开始一个事务 start transaction, set autocommit=off;
事务隔离级别
案例:
设置事务隔离级别
-- 演示mysql的事务隔离级别
-- 1. 开了两个mysql的控制台
-- 2. 查看当前mysql的隔离级别
SELECT @@tx_isolation;
-- mysql> SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+
-- 3.把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 4. 创建表
CREATE TABLE `account`(
id INT,
`name` VARCHAR(32),
money INT);
-- 查看当前会话隔离级别
SELECT @@tx_isolation
-- 查看系统当前隔离级别
SELECT @@global.tx_isolation
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]
-- mysql默认的事务隔离级别是可重复读repeatable read
-- 修改默认隔离级别:mysql目录下my.ini添加语句
transaction-isolation=READ-UNCOMMITTED
-- 后重启dos:net stop mysql->net start mysql
事务 ACID
事务的 acid 特性
表类型和存储引擎
主要的存储引擎/表类型特点
细节说明
使用案例
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES;
-- innodb 存储引擎，是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁
-- myisam 存储引擎
CREATE TABLE t28
(id INT,
`name` VARCHAR(32)) ENGINE MYISAM;
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
START TRANSACTION;
SAVEPOINT a;
INSERT INTO t28 VALUES(1,'jack');
SELECT * FROM t28;
ROLLBACK TO a;
-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了Mysql服务，数据丢失, 但是表结构还在]
-- 2. 执行速度很快(没有IO读写) 3. 默认支持索引(hash表)
CREATE TABLE t29
(id INT,
`name` VARCHAR(32)) ENGINE MEMORY;
DESC t29;
INSERT INTO t29 VALUES(1,'tom'),(2,'mary'),(3,'scott');
SELECT * FROM t29;
-- 修改存储引擎
ALTER TABLE t29 ENGINE=INNODB;
如何选择
修改存储引擎
视图
概念
基本使用
案例
-- 视图的使用
-- 创建一个视图emp_view01，只能查询emp表的(empno、ename, job 和 deptno ) 信息
-- 创建视图
CREATE VIEW emp_view01
AS
SELECT empno,ename,job,deptno FROM emp;
-- 查看视图
DESC emp_view01;
SELECT * FROM emp_view01;
SELECT empno,job FROM emp_view01;
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01;
-- 删除视图
DROP VIEW emp_view01;
-- 视图的细节
-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm) 没有数据文件.ibd
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
-- 修改视图 会影响到基表
UPDATE emp_view01 SET job='MANAGER' WHERE empno=7369;
SELECT * FROM emp; -- 查询基表
SELECT * FROM emp_view01;
-- 修改基本表， 会影响到视图
UPDATE emp SET job='SALESMAN' WHERE empno=7369;
-- 3. 视图中可以再使用视图 , 比如从emp_view01 视图中，选出empno,和ename做出新视图
DESC emp_view01;
CREATE VIEW emp_view02
AS
SELECT empno,ename FROM emp_view01;
SELECT * FROM emp_view02;
视图细节讨论
视图最佳实践
练习
-- 视图的课堂练习
-- 针对 emp ，dept , 和 salgrade 张三表.创建一个视图 emp_view03，
-- 可以显示雇员编号，雇员名，雇员部门名称和 薪水级别[即使用三张表，构建一个视图]
/*
分析: 使用三表联合查询，得到结果
将得到的结果，构建成视图
*/
-- 遍历三张表
SELECT * FROM emp,dept,salgrade;
-- 按条件创建视图
CREATE VIEW emp_view03
AS
SELECT empno,ename,dname,grade
FROM emp,dept,salgrade
WHERE emp.deptno=dept.deptno
AND (sal BETWEEN losal AND hisal);
DROP VIEW emp_view03;
DESC emp_view03;
SELECT * FROM emp_view03;
管理
创建用户
删除用户
用户修改密码
权限
给用户授权
回收用户授权
权限生效指令
练习
SELECT * FROM `user`;
SELECT PASSWORD('123456') FROM DUAL;
*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
-- 演示 用户权限的管理
-- 创建用户 shunping 密码 123 , 从本地登录
CREATE USER 'zxk'@'localhost' IDENTIFIED BY '123';
-- 使用root 用户创建 testdb ,表 news
CREATE DATABASE testdb;
CREATE TABLE news
(id INT,
content VARCHAR(32));
INSERT INTO news VALUES(100,'北京新闻');
-- 给 shunping 分配查看 news 表和 添加news的权限
GRANT SELECT,INSERT ON testdb.news TO 'zxk'@'localhost' ;
-- 可以增加update权限
GRANT UPDATE ON testdb.news TO 'zxk'@'localhost' ;
-- 修改 shunping的密码为 abc
SET PASSWORD FOR 'zxk'@'localhost'=PASSWORD('abc');
-- 回收 shunping 用户在 testdb.news 表的所有权限
REVOKE SELECT,INSERT,UPDATE ON testdb.news FROM 'zxk'@'localhost';
REVOKE ALL ON testdb.news FROM 'zxk'@'localhost';
-- 删除 shunping
DROP USER 'zxk'@'localhost';
细节说明
-- 说明 用户管理的细节
-- 在创建用户的时候，如果不指定Host, 则为% , %表示表示所有IP都有连接权限
-- create user xxx;
CREATE USER jack;
SELECT * FROM `user`;
SELECT `host`,`user` FROM mysql.user;
-- 你也可以这样指定
-- create user 'xxx'@'192.168.1.%' 表示 xxx用户在 192.168.1.*的ip可以登录mysql
CREATE USER 'mary'@'192.168.1.%';
-- 在删除用户的时候，如果 host 不是 %, 需要明确指定 '用户'@'host值'
DROP USER jack;-- 默认就是 DROP USER 'jack'@'%'
DROP USER 'mary'@'192.168.1.%';