一、长时间没有回顾sql语句了,今天对mysql进行回顾,总结以下吧......
二、新建数据库:
字符集:一般选择utf8就行,还有一个比较像的utf8mb4,utf8mb4兼容utf8,而且进行了扩充,能够表示更多的字符集,但是我们常用的用utf8就够了,如果需要,可以用utf8mb4。 放一个博主的unicode编码区的总结。
http://blog.csdn.net/leelyliu/article/details/52879685
排序规则:是对字符集下不同字符的比较规则。有几个特征:
1、两个不同的字符集不能有相同的排序规则;
2、两个字符集有一个默认的排序规则;
3、排序一般分为两种: utf_bin和utf_general_ci 。bin 是二进制, a 和 A 会别区别对待.
例如你运行:SELECT * FROM table WHERE txt = 'a'
那么在utf8_bin中你就找不到 txt = 'A' 的那一行, 而 utf8_general_ci 则可以.
utf8_general_ci 不区分大小写,这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写,如果用户名和邮箱用这个 就会照成不良后果。
utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写,而且可以存二进制的内容
utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
utf8_general_ci校对速度快,但准确度稍差。 (准确度够用,一般建库选择这个)
utf8_unicode_ci准确度高,但校对速度稍慢。
三、用SQL Yog新建数据库exer01,如果用sql语句新建数据库,有自己默认的字符集和排序规则;
1、默认的字符集是utf8,排序规则是utf8_general_ci;
2、sql语句:
单表操作:
CREATE DATABASE IF NOT EXISTS exer01; -- 如果不存在,就新建数据库
USE exer01; -- 使用当前数据库
SHOW DATABASES; -- 查询所有数据库的名称
SHOW CREATE DATABASE exer01; -- 查询某个数据库的字符集和创建该数据库的sql语句
DROP DATABASE IF EXISTS exer01; -- 如果存在,则删除数据库
SELECT DATABASE(); -- 查询当前正在使用的数据库,记得加括号
ALTER DATABASE exer01 CHARACTER SET gbk; -- 修改数据库的字符集
-- 创建表
CREATE TABLE table1(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增
NAME VARCHAR(20), -- 字符串,最大20个
birth DATE, -- 日期类yyyy-MM-dd
score DOUBLE(4,1), -- 浮点数,总共4位,一位小数
time1 TIMESTAMP -- 时间戳,yyyy-MM-dd HH:mm:ss,如果不给这个赋值,默认保持系统时间
);
ALTER TABLE table1 RENAME TO table2; -- 将table1修改表名位table2
ALTER TABLE table2 CHARACTER SET gbk; -- 修改表的字符集
SHOW CREATE TABLE table2; -- 查询当前操作表的字符集
ALTER TABLE table2 ADD gender VARCHAR(10);-- 添加列
ALTER TABLE table2 MODIFY gender INT; -- 修改列的属性
SELECT * FROM table2; -- 查询表
ALTER TABLE table2 DROP gender; -- 删除列
INSERT INTO table2(id,NAME,birth,score,time1)
VALUES (NULL,'Tom','2021-10-11',103.4,NULL),
(NULL,'Tom','2021-10-11',103.4,NULL); -- 添加数据
TRUNCATE TABLE table2; -- 清空表中的数据
-- 练习
CREATE TABLE studentscore(
id INT,-- 编号
NAME VARCHAR(20),-- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学成绩
english INT -- 英语成绩
);
INSERT INTO studentscore(id,NAME,age,sex,address,math,english) VALUES(1,'马云',23,'男','杭州',89,92),
(2,'雷军',45,'男','北京',56,78),
(3,'周杰伦',34,'男','台湾',65,66),(4,'刘德华',65,'男','澳门',88,90),(5,'张学友',37,'男','澳门',94,45),
(6,'张碧晨',23,'女','北京',55,67),(7,'柳岩',26,'女','北京',87,66),(8,'马化腾',36,'男','上海',90,NULL);
SELECT * FROM studentscore; -- 查询表
SELECT * FROM studentscore
WHERE
age>=30; -- 查询年龄大于30岁的人
SELECT * FROM studentscore
WHERE
age
BETWEEN 20 AND 80; -- 查询年龄在20岁到80岁之间的人;
SELECT * FROM studentscore
WHERE
NAME
LIKE '%马%'; -- 查询名字里面有马的人
SELECT * FROM studentscore
ORDER BY age ASC,math DESC; -- 按年龄升序排序,年龄一样的,按照数学车成绩降序排序
-- 聚合函数:count:计算个数、max:计算最大值、min:计算最小值、sum:计算和、avg:计算平均值,聚合函数需要属性值为空
SELECT COUNT(english) FROM studentscore; -- 自动排除english为0的情况;
SELECT COUNT(IFNULL(english,0)) FROM studentscore; -- 如果english为null,则赋值为0,重新计数
SELECT MAX(age) FROM studentscore; -- 求取最大年龄
-- 分组
SELECT sex,AVG(math),COUNT(IFNULL(english,0))
FROM studentscore GROUP BY sex; -- 按性别分组查询
SELECT sex,AVG(math),COUNT(IFNULL(english,0))
FROM studentscore WHERE math>60 GROUP BY sex; -- 要求math低于60的不进行排序
SELECT sex,AVG(math),COUNT(IFNULL(english,0))
FROM studentscore WHERE math>60 GROUP BY sex
HAVING COUNT(IFNULL(english,0))>1; -- having在分组之后,分组小于1则不会被查找出来
-- 分页查询
SELECT * FROM studentscore LIMIT 0,3; -- 0代表开始的索引,3代表每页显示的数据数量
SELECT * FROM studentscore LIMIT 4,4;
ALTER TABLE studentscore DROP PRIMARY KEY; -- 删除主键
ALTER TABLE studengscore MODIFY id INT PRIMARY KEY; -- 创建表之后添加主键
多表操作:
-- 多表练习
CREATE TABLE dept(
id INT PRIMARY KEY, -- 部门id主键
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
SELECT * FROM dept;
-- 职务表,职务名称,职务描述
CREATE TABLE job(
id INT PRIMARY KEY,
jname VARCHAR(20),
jdescription VARCHAR(50)
);
-- 添加四个职务
INSERT INTO job VALUES
(1,'董事长','管理整个公司,接单'),
(2,'经理','管理部门员工'),
(3,'销售员','向客人推销产品'),
(4,'文员','使用办公软件');
SELECT * FROM job;
-- 员工表
CREATE TABLE emp(
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT, -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT jobidfk FOREIGN KEY (job_id) REFERENCES job(id),
CONSTRAINT deptfk FOREIGN KEY (dept_id) REFERENCES dept(id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id)VALUES
(1001,'梁朝伟',4,1004,'2007-9-12',7900.00,NULL,20),
(1002,'张国荣',3,1006,'2008-1-27',8500.00,3000.00,30),
(1003,'孙红雷',3,1006,'2003-9-19',9000.00,5000.00,30),
(1004,'张碧晨',2,1009,'2012-2-22',5000.00,NULL,20),
(1005,'郭富城',4,1006,'2019-9-13',12000.00,14000.00,30),
(1006,'刘德华',2,1009,'2015-4-20',7900.00,NULL,30),
(1007,'张学友',2,1009,'2004-9-19',7000.00,NULL,10),
(1008,'曾志伟',4,1004,'2016-5-12',6000.00,NULL,20),
(1009,'黎明',1,NULL,'2003-2-15',13000.00,NULL,10),
(1010,'黄渤',3,1006,'2012-4-30',9000.00,0.0,30),
(1011,'张娜拉',4,1004,'2019-7-14',10000.00,NULL,20),
(1012,'孙倩雯',4,1006,'2015-1-16',10900.00,NULL,30),
(1013,'周润发',4,1004,'2004-5-23',9000.00,NULL,20),
(1014,'周星驰',4,1007,'2016-9-02',11000.00,NULL,10);
SELECT * FROM emp;
-- 工资等级表
CREATE TABLE salargrade(
grade INT PRIMARY KEY, -- 等级
losalary INT, -- 最低薪资
hisalary INT -- 最高薪资
);
-- 添加5个薪资等级
INSERT INTO salargrade VALUES
(1,1000,5000),
(2,5010,8000),
(3,8010,10000),
(4,10010,12000),
(5,12010,20000);
SELECT * FROM salargrade;
-- 1、查询所有员工信息,员工编号、姓名,薪资,职务名称、职务描述
SELECT
e.`id`,
e.`ename`,
e.`salary`,
j.`jname`,
j.`jdescription`
FROM
emp e,job j
WHERE
e.`job_id`=j.`id`;
-- 2、查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称、部门位置
SELECT
e.`id`,
e.`ename`,
e.`salary`,
j.`jname`,
j.`jdescription`,
d.`dname`,
d.`loc`
FROM
emp e,job j,dept d
WHERE
e.`job_id`=j.`id`
AND
e.`dept_id`=d.`id`
-- 3、查询员工姓名、工资、工资等级
SELECT
e.`ename`,
e.`salary`,
s.grade
FROM
emp e,salargrade s
WHERE
e.`salary`
BETWEEN
s.losalary
AND
s.hisalary
-- 4、查询员工姓名、工资,职务名称、职务描述、部门名称、部门位置、工资等级
SELECT
e.`ename`,
e.`salary`,
j.`jname`,
j.`jdescription`,
d.`dname`,
d.`loc`,
s.grade
FROM
emp e,
job j,
dept d,
salargrade s
WHERE
e.`job_id`=j.`id`
AND
e.`dept_id`=d.`id`
AND
e.`salary` BETWEEN s.losalary AND s.hisalary;
-- 5、查询部门编号、部门名称、部门位置、部门人数
SELECT
d.`id`, -- 部门id
d.`dname`, -- 部门名称
d.`loc`, -- 部门地址
t.total -- 部门人数
FROM
dept d,
(SELECT dept_id,COUNT(id) total -- 虚拟表,先按照部门id分组查询出各部门的人数
FROM emp
GROUP BY dept_id
)t
WHERE
t.dept_id=d.`id`
-- 6、查询所有员工的姓名以及直接上级的姓名,没有领导的员工也需要查询
-- 自关联映射
SELECT
e1.ename,
e1.mgr,
e2.id,
e2.ename
FROM
emp e1
LEFT JOIN
emp e2
ON
e1.mgr=e2.id
先到这里,后续再补充吧~