mysql学习笔记代码

/*
设计一张员工信息表，要求如下：
1、编号（纯数字）
2、员工工号（字符串类型，长度不超过10位）
3、员工姓名（字符串类型，长度不超过10位）
4、性别（男/女，存储一个汉字）
5、年龄（正常人年龄，不可能存储负数）
6、身份证号（二代身份证均为18位
7、入职时间（取值为年月日即可）
*/ USE hrs;
CREATE TABLE emp (
	id INT COMMENT '编号',
	workno VARCHAR ( 10 ) COMMENT '工号',
	NAME VARCHAR ( 10 ) COMMENT '姓名',
	gender CHAR ( 1 ) COMMENT '性别',
	age TINYINT UNSIGNED COMMENT '年龄',
	idcard CHAR ( 18 ) COMMENT '身份证号',
	entrydate date COMMENT '入职时间' 
) COMMENT '员工表';###查看整个表
DESC emp;#1——————表操作---修改/删除
##添加字段
##ALTER TABLE 表名 ADD 字段名 类型（长度） [COMMENT 注释] [约束];
ALTER TABLE emp ADD nickname VARCHAR ( 20 ) COMMENT '昵称';###查看整个表
DESC emp;##修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）；
##修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度) [COMMENT 注释] [约束]; 
##列子：将emp表的nickname字段修改为username,类型为varchar(30)
ALTER TABLE emp CHANGE nickname username VARCHAR ( 30 ) COMMENT '用户名';###查看整个表
DESC emp;#2————表删除
##删除字段：ALTER TABLE 表名 DROP 字段名；
##列子：将emp表的字段username删除
ALTER TABLE emp DROP username;###查看整个表
DESC emp;##修改表名：ALTER TABLE 表名 RENAME TO 新表名；
##将emp表的表名修改为employee
ALTER TABLE emp RENAME TO employee;
SHOW TABLES;##删除表：DROP TABLE [IF EXISTS] 表名;
##删除指定表，并重新创建该表：TRUNCATE TABLE 表名;
DROP TABLE
IF
	EXISTS tb_emp;
TRUNCATE TABLE employee;
SHOW TABLES;###查看整个表
DESC employee;#插入
##DML-添加数据
##1、给指定字段添加数据：INSERT INTO 表名(字段名1，字段名2,...) VALUES(值1,值2,...);
##2、给全部字段添加数据：INSERT INTO 表名 VALUES(值1,值2,...);
##3、批量添加数据：两种方式
##第一种：INSERT INTO 表名(字段名1，字段名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
#第二种：INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
/*
注意：
1、插入数据时，指定的字段顺序需要与值的顺序时一一对应的。
2、字符串和日期型数据应该包含在引号中。
3、插入的数据大小，应该在字段的规定范围内。
*/#1、给指定字段添加数据：
INSERT INTO employee ( id, workno, NAME, gender, age, idcard, entrydate )
VALUES
	( 1, '1', 'Itcast', '男', 10, '123456789012345678', '2000-01-01' );##查询表
SELECT
	* 
FROM
	employee;#2、给全部字段添加数据
INSERT INTO employee
VALUES
	( 2, '2', '张无忌', '男', 18, '123456789012345678', '2005-01-01' );#3、批量添加数据
INSERT INTO employee
VALUES
	( 3, '3', '韦一笑', '男', 38, '123456789012345678', '2005-01-01' ),(
		4,
		'4',
		'赵敏',
		'女',
		18,
		'123456789012345678',
		'2005-01-01' 
	);#DML-修改数据
##UPDATE  表名 SET 字段名1 =值1,字段名2=值2,...[WHERE 条件];
##注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
-- 修改id 为1的数据，将name修改为itheima
UPDATE employee 
SET NAME = 'itheima' 
WHERE
	id = 1;-- 修改id 为1的数据，将name修改为小昭 ,gender修改为女
UPDATE employee 
SET NAME = '小昭',
gender = '女' 
WHERE
	id = 1;-- 所有员工的入职日期修改为2008-01-01
UPDATE employee 
SET entrydate = '2008-01-01';##DML-删除数据：DELETE FROM 表名 [WHERE 条件];
/*
注意：
DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
DELETE语句不能删除某一个字段的值（可以使用UPDATE）
*/-- 删除gender为女的员工
DELETE 
FROM
	employee 
WHERE
	gender = '女';-- 删除所有员工
DELETE 
FROM
	employee;
/*
DQL-语法
SELECT
      字段列表
FROM
		  表名列表
WHERE
			条件列表
GROUP BY			
			分组字段列表
HAVING		
			分组后条件列表
ORDER BY		
			排序字段列表
LIMIT		
			分页参数
*/

ALTER TABLE emp ADD workdress char(20) comment '工作地址';
desc emp;
INSERT INTO emp( id, workno, NAME, gender, age, idcard, entrydate,workdress)
VALUES
	( 1, '1', 'Itcast', '男', 10, '123456789012345678', '2000-01-01','北京');##查询表
INSERT INTO emp
VALUES
	( 2, '2', '张无忌', '男', 18, '123456789012345678', '2005-01-01' ,'北京');##查询表
INSERT INTO emp
VALUES
	( 3, '3', '韦一笑', '男', 38, '123456789012345678', '2005-01-01','上海' ),(
		4,
		'4',
		'赵敏',
		'女',
		18,
		'123456789012345678',
		'2005-01-01' ,'北京');##查询表
		
insert into emp values(5,'5','急吼吼','女',43,'123456789012345678','2004-04-03','湖南');
insert into emp values(6,'6','急吼','女',23,'NULL','2004-04-03','湖南');
insert into emp values(7,'7','吼','男',88,'123456789012345678','2004-04-03','湖南');
insert into emp values(8,'8','急','女',45,'123456789012345678','2004-04-03','湖南');
insert into emp values(9,'9','南','女',55,'12345678901234567X','2004-04-03','湖南');
#基本查询
##1、查询多个字段：SELECT 字段1,字段2,字段3,...FROM 表名;
SELECT * FROM 表名; 
##2、设置别名：SELECT 字段1[AS别名1],字段2[AS别名2]...FROM 表名
##3、去除重复记录：SELECT DISTINCT 字段列表 FROM 表名;
desc emp;
##列子：
##1、查询指定字段name,workno,age返回
select name,workno,age from emp;
##2、查询所有字段返回
select id,workno,name,gender,age,idcard,entrydate,workdress from emp;
select *from emp;
##3、查询所有员工的地址，起别名
select workdress as '工作地址' from emp;
##也可以这样写：
select workdress '工作地址' from emp;
##4、查询公司员工的上班地址(不要重复）
select distinct workdress '工作地址' from emp;

#条件查询(WHERE)
##1、语法：SELECT 字段列表 FROM 表名 WHERE 条件列表;
###列子：
##1、查询年龄等于88的员工
select * from emp where age=88;
##2、查询年龄小于20的员工信息
select * from emp where age<20;
##3、查询年龄小于20等于20的员工的信息
select *from emp where age<=20;
##4、查询没有身份证号的员工信息
select *from emp where idcard is NULL;
##5、查询有身份证号的员工信息
select *from emp where idcard is not null;
##6、查询年龄不等于88的员工信息
select * from emp where age!=88;
select * from emp where age<>88;
##7、查询年龄在15岁（包含）到20岁（包含）之间的员工信息
select * from emp where age>=15 && age<=20;
select * from emp where age>=15 and age<=20;
select *from emp where age between 15 and 20;
##8、查询性别为女且年龄小于25的员工信息
select * from emp where age<25 && gender='女';
select * from emp where age<25 and gender='女';
##9、查询年龄等于18或20或40的员工信息
select * from emp where age=18 or age=20 or age=40;
select * from emp where age in(18,20,40);
##10、查询姓名为两个字的员工信息
select * from emp where name like '__';
##11、查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';
select * from emp where  idcard like '_________________X';

#聚合函数(count、max、min、avg、sum)
##DQL-聚合函数
###将一列数据作为一个整体，进行纵向计算
###常见聚合函数:count、max、min、age、sum
###语法：SELECT 聚合函数(字段列表) FROM 表名;
###注意：null值不参与所有聚合函数运算
###1、统计该企业员工数量
select count(*) from emp;
select count(id) from emp;
select count(idcard) from emp;
###2、统计该企业员工的平均年龄
select avg(age) from emp;
###3、统计该企业员工的最大年龄
select max(age) from emp;
###4、统计该企业员工的最小年龄
select min(age) from emp;
###5、统计湖南地区员工的工年龄之和
select sum(age) from emp where workdress='湖南';

#分组查询(GROUP BY)
###语法：SELECT 字段列表 FROM  表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING  分组后过滤条件];
###where与having区别：
#####执行时机不同：where是分组之前进行过滤，不满足where条件,不参与分组，而having是分组之后对结果进行过滤
#####判断条件不同：where不能对聚合函数进行判断，而having可以
####注意：
######执行顺序:where>聚合函数>having
######分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
###列子：
#####1、根据性别分组，统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender;
#####2、根据性别分组，统计男性员工和女性员工的平均年龄
select gender,avg(age) from emp group by gender;
#####3、查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workdress,count(*) from emp where age<45 group by workdress having count(*)>=3;
#address_count别名
select workdress,count(*) address_count from emp where age<45 group by workdress having count(*)>=3;


#排序查询(ORDER BY)
###语法：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;
###排序方式：ASC(升序 默认值),DESC(降序)
####注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
####列子：
#####1、根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
#####2、根据入职时间，对员工进行降序排序
select * from emp order by entrydate desc;
#####3、根据年龄对公司的员工进行升序排序，年龄相同，在按照入职时间进行降序排序
select * from emp order by age asc,entrydate desc;


#分页查询(LIMIT)
###语法：SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数
#####注意：
######起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
######分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
######如果查询的第一页是数据，起始索引可以省略，直接简写为limit 10
####列子：
#####1、查询第1页员工数据，每页显示10条记录
select * from emp limit 0,10;
#####2、查询第2页员工数据，每页展示10条记录------>(页码-1)*页展示记录数======>(2-1)*10=10,即前面的是10，后面是10条记录
select * from emp limit 10,10;