MySQL数据库的单表基本操作(二，附加职工小练习）

数据库的增删查改是基础，所以小编要增加练习，与增加训练来增加自己的基础，确实上百度上有许多的博客，大家可以参考参考多加练习。废话不多说，开干。

表的约束：

NOT NULL	非空
UNIQUE Key	唯一键
PRIMARY KEY	主键
FOREIGN KEY	外键
CHECK	检查

check检查约束：

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

删除表的约束：

删除NOT NULL约束	alter table 表名 modify 列名 类型;
删除UNIQUE约束	alter table 表名 drop index 惟一约束名;
删除PRIMARY KEY约束	alter table 表名 drop primary key;
删除FOREIGN KEY约束	alter table 表名 drop foreign key 外键名;

练习：

#单个约束
CREATE TABLE  t_text2 ( id int primary key auto_increment comment 'primary key auto_increment 设置自增加主键',
		name varchar(20) not null unique comment 'not null unique 设置不能为空', 
		age int NOT NULL ,
		CHECK (age > 0 )   #设置检查约束条件age必须大于0

		);
		#CONSTRAINT t_text2 CHECK (Id>0 AND name='小明')#多个约束

授权（create）、创建用户、删除用户、
撤销权限（revoke）

生产环境授权用户建议： 
1、博客，CMS等产品的数据库授权 select,insert,update,delete,create 库生成后收回create权限 
2、生产环境主库用户授权 select,insert,update,delete 
3、生产环境从库授权 select

创建用户练习：
方式一：
flush privileges;#冲洗特权
CREATE USER lsf666@'localhost'  IDENTIFIED BY '666666'; #必须加第一行，不然返回1290这个错误，所以执行失败


方式二：
INSERT INTO mysql.user(user,host, authentication_string,ssl_cipher, x509_issuer,x509_subject)#具体解释插入mysql。用户(用户，主机，authentication_string,ssl_cipher, x509_issuer,x509_subject)
VALUES('haha','localhost',password('ABCabc123!'),'','',''); #自己添加用户信息
FLUSH PRIVILEGES;#冲洗特权;

方法三：
GRANT SELECT ON *.* TO lalala@'localhost' IDENTIFIED BY '123456'; 
FLUSH PRIVILEGES;


sql语句查看用户命令：
SELECT User FROM mysql.user;

现象：

聚合函数：（SQL官方提供了一个用来常见运算的一些）

count()	统计数据
max()	最大值
min()	最小值
avg()	平均值

练习：
use db-shuihu #登录数据库
SELECT count(id) count from t_shuihuo; #统计表中id的数量
select cond(列名) count from 表名 #其他函数方法使用一样，改 cond(列名) count 相应的值

别名（alias）的使用：（需要查询的字段 as 新的名称，注意:AS可以省略不写）
练习：

SELECT min(id) AS 'haha' from t_sanguo;
SELECT min(id)  haha from t_sanguo;

模糊查询：（模糊查询是放弃使用索引，全表扫描的操作，因此效率非常低）

	
--  查询以拼开头的用户
SELECT u.name, id  from t_shuihuo u where name like "拼%";
  
# 包含时字的名称
SELECT u.name, id  from t_shuihuo u where name like "%时%";

# 第二个字是者字的 _ 表示一个位
SELECT u.name, id  from t_shuihuo u where name like "_者%";

排序 ：

SELECT * FROM t_sanguo ORDER BY id ;			# 默认升序排序
SELECT * FROM t_sanguo ORDER BY id asc;  # 升序排序
SELECT * FROM t_sanguo ORDER BY id DESC;	# 降序排序
SELECT * FROM 表名称 ORDER BY 排序的根据 ;  #必须类型是int

分组：（将内容相同的归纳为一组，使用group by 字段）

#查询每个位置的个数
SELECT COUNT(*),id FROM  t_sanguo
GROUP BY id;
SELECT 分组条件 FROM  表名
GROUP BY ;

筛选：（having ，分组后的结果进行筛选，分组函数做筛选不能放在where后面）

SELECT 分组条件 , count(分组条件) from 表名 where age > 5 GROUP BY gender HAVING COUNT(gender) > 2; #age > 5 GROUP BY gender HAVING COUNT(gender) > 2 ；是筛选条件

分页：

SELECT * from t_sanguo LIMIT 0, 3;#按3个分一页
SELECT * from t_sanguo LIMIT 3, 3;
select * from t_user limit num;				# 限制显示num条 
select * from t_user limit 起始位, 每页显示的条数;	# 限制显示num条 

程序员要在界面上实现分页，需要四个变量：

当前页：	pageNow			# 用户决定
	每页数量：	pageSize		# 程序员决定
	数据总量：	pageAll			# 查询
	总页数：	pageCount		# 计算 pageCount = pageAll % pageCount== 0 ? pageAll / pageCount : pageAll / pageCount + 1


  当前页=select * from 表名称 where 条件 xxxx limit (pageNow - 1)*pageSize, pageSize;

一个完整的复杂查询的顺序问题：

select 字段1, ...
from 表名称1, ...
[where 条件（最先执行）]
[group by 分组字段 ]
[having 分组后的筛选]
[order by 排序]
[limit 分页]

小练习：

一、单表查询
	素材： 
	表名：worker-- 表中字段均为中文，比如 部门号 工资 职工号 参加工作 等
	CREATE TABLE `worker` (
	 `部门号` int(11) NOT NULL,
	 `职工号` int(11) NOT NULL,
	 `工作时间` date NOT NULL,
	 `工资` float(8,2) NOT NULL,
	 `政治面貌` varchar(10) NOT NULL DEFAULT '群众',
	 `姓名` varchar(20) NOT NULL,
	 `出生日期` date NOT NULL,
	 PRIMARY KEY (`职工号`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

	INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');
	INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');
	INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');
	INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');
	INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');
	INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');

	1、显示所有职工的基本信息。
	   select * from worker; 
	2、查询所有职工所属部门的部门号，不显示重复的部门号。
	  select DISTINCT `部门号` from worker ；
	3、求出所有职工的人数。
	  select COUNT(*)  '人数' from worker ；
	4、列出最高工和最低工资。
	   select MAX(工资),min(工资)   from worker ；
	5、列出职工的平均工资和总工资。
	   select avg(工资)  平均工资,count(工资) as 总工资   from worker ；
	6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
	 CREATE TABLE `工作日期表`(	 
		`职工号` int(11) NOT NULL ,
		`姓名` varchar(20) NOT NULL ,
		`参加工作` varchar(20) ,
		PRIMARY KEY (`职工号`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
	7、显示所有女职工的年龄。
	 #没有给男生女生，决定全部说女生。所以说6个
	8、列出所有姓刘的职工的职工号、姓名和出生日期。
	SELECT 职工号 , 姓名 ,出生日期 FROM worker WHERE 姓名 LIKE "刘%" ;
	9、列出1960年以前出生的职工的姓名、参加工作日期。
	SELECT 姓名 , 工作时间  FROM worker WHERE 出生日期 < 1960 ;
	10、列出工资在1000－2000之间的所有职工姓名。 
	SELECT 姓名 FROM worker WHERE 2000>工资>1000;
	11、列出所有陈姓和李姓的职工姓名。
	SELECT `姓名` FROM worker WHERE `姓名` like  "李%";
	SELECT `姓名` FROM worker WHERE `姓名` like  "陈%"; 
	或者
	SELECT `姓名` FROM worker WHERE     ( 姓名 LIKE "陈%" ) or (  姓名 LIKE "李%") ;
	12、列出所有部门号为2和3的职工号、姓名、党员否。
	  SELECT 职工号 , 姓名 , 政治面貌 FROM worker WHERE 部门号 LIKE "%2";
SELECT 职工号 , 姓名 , 政治面貌 FROM worker WHERE 部门号 LIKE "%3"  ;

	13、将职工表worker中的职工按出生的先后顺序排序。
	SELECT * FROM worker ORDER by 出生日期 ;
	14、显示工资最高的前3名职工的职工号和姓名。 
	SELECT * FROM  worker  ORDER BY 工资 ASC LIMIT 3;
	15、求出各部门党员的人数。
	 SELECT count(*) FROM  worker  WHERE 政治面貌 = '党员' ;
	
	16、统计各部门的工资和平均工资
	SELECT avg(工资),sum(工资) FROM  worker WHERE 部门号 = 101 ;
	SELECT avg(工资),sum(工资) FROM  worker WHERE 部门号 = 102 ;
	17、列出总人数大于4的部门号和总人数。
	SELECT count(*) as 总人数,部门号 FROM  worker GROUP BY 部门号 HAVING COUNT(*)> 4 ;

现象：