MySql基本操作语句回顾

一、长时间没有回顾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

先到这里，后续再补充吧~

---