【无标题】

创建表的完整语法

create table t1(
	id int,
    name varchar(43),
    age int
);


create table 库名.表名(
	字段名1 数据类型 约束条件 约束条件 约束条件 约束条件,
    字段名2 数据类型 约束条件 约束条件 约束条件 约束条件,
    字段名3 数据类型 约束条件 约束条件 约束条件 约束条件
);

insert into 库名.t1 values('1', 2, 3, 4, 5, 6);

1. 字段名和数据类型必须写的，不能省略
2. 约束条件是可选的，可有可无，而且可有有多个
3. 表结构中最后一个字段不能有逗号

约束条件

约束条件其实就是在数据类型的基础之上在做约束
1. unsigned # 无符号
	id int unsigned
    
2. zerofill # 0填充
3. default # 默认值
	create table t4 (id int, name varchar(32) default 'kevin');
    insert into t4 values(1, 'jerry');
    insert into t4(id) values(1);
4. not null # 非空
	create table t5 (id int, name varchar(32) not null);
    insert into t5(id) values(1);
    
5. unique # 唯一
	单列唯一
    create table t6 (id int, name varchar(32) unique);
    多列唯一
    create table t7 (
        id int, 
        ip varchar(32), 
        port varchar(32),
        unique(ip, port)
    );
6. 主键(primary key)
	"""主键单纯从约束上来看，它相当于是非空且唯一 unique not null"""
    id unique not null ---------> id primary key 
    create table t8 (id int primary key);
    create table t8 (id int unique not null);
    # 主键本身是一种索引，索引能够加快查询速度
    
    InnoDB存储引擎规定每一张表都要有一个主键，但是，我之前创建的表都没有指定主键, 表是怎么创建成功的? 
    """
    	是因为InnoDB存储引擎内部有一个隐藏的主键，这个主键我们看不到，它也不能够加快查询速度，仅仅是为了帮助我们把表创建成功. 所以，以后我们创建表的时候都主动的创建一个主键，我们自己创建的主键能够加快查询速度，因为是一个索引.
    """
    一般情况下，主键应该创建哪个字段? 大多都给id字段加了，所以，每一张表都要有一个id字段，并且一张表中不只是有一个主键，可以有多个主键，但是，大多数情况下，都只有一个
    
    主键一般都给id aid sid uid pid ...
    create table t(
        id int primary key,
        name varchar(32)
    )
    # 我们可以通过主键确定一张表中得唯一一条记录!!!
7. auto_incrment
	# 自增：每一次主动比上一次加1
    """一般情况下，它配合主键使用"""
    create table t9 (
    	id int primary key auto_increment,
        name varchar(32)
    );

整型中括号中得数字的作用

id int(10)       # 数字不代表的是范围，代表显示宽度  没啥作用 一般直接写 int
name varchar(32) # 数字代表的就是存储的范围 最大长度65535个字节（一行）

因为varchar(32) 跟char(32) 不同 后者是固定的长度 前者则需要另外存储长度，减去一个字节存是否为空的

标志位，减去一个字节存字符长度（255以内），字符长度超过255，则需要要两个字节存储长度，因此可用长度
是65532个字节，utf8编码情况下，一个字符占3个字节 65532/3 = 28844 这就是可用最大长度(单位：字符)，
如果一行内还有其他字段 则需要减去其他字段的长度，例如int类型,占四个字节则最少减去两个字符，即6个字
节(utf8下)，可用最大长度:28844-2 ，

create table t1(id int(3));
create table t2(id int(9));
insert into t2 values(9);


create table t3(id int(9) zerofill);
insert into t3 values(9);

清空表的两种方式

1. delete from t; # 不会重置id值
2. truncate t9;   # 清空表、重置id值
"""truncate：建议使用truncate，使用这个，万一你清空错了，还有机会恢复"""
mysql它有很多个日志文件，binlog日志-----》可以恢复数据，记录了你所有的SQL语句

补充一些其他的SQL语句

-- 对表的修改

-- 增加字段  after后跟字段名 表示添加到哪个字段后面  如果放到第一位 就是first
alter table t1 add name char(4) unique not null after id;

-- 删除字段
alter table t1 drop name;

-- 修改字段
-- 修改表名
alter table t1 rename name Name;
-- 修改字段类型(只能修改字段类型，不能修改字段名，该字段有数据的情况下修改为其他类型，会报错)
alter table t1 modify name char(11);
-- 既要修改字段名，又要修改字段类型（少修改一个就会报错）
alter table t1 change name Name char(12);

 

 MySQL表格--关键字--小练习
数据准备
设定表格式

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  sex enum('male','female') not null default 'male', #大部分是男的
  age smallint(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);
写入数据

insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('tom','male',78,'20150302','teacher',1000000.31,401,1),#以下是教学部
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

 写入数据后的页面

查询关键字之where
where------>筛选条件的

 模糊查询:没有明确的筛选条件
    关键字:like
    关键符号:
        %:匹配任意个数任意字符
        _:匹配单个个数任意字符
        show variables like '%mode%';

 1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select *  from emp where id between 3 and 6;  
2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);  # 简写
 3.查询员工姓名中包含o字母的员工姓名和薪资
select name,salary from emp where name like '%o%';
4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
5.查询id小于3或者大于6的数据
select *  from emp where id not between 3 and 6;
6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);
7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL;  # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;
 
'''在sql中，NULL和''不一样''
查询关键字之group by分组
分组: 按照某个指定的条件将单个单个的个体分成一个个整体

        在MySQL中分组之后，只能够获得分组的依据! 按照哪个字段分组就只能获取这个字段的值，别的字段不能拿到

分组一般配合聚合函数使用：
            sum max min avg count 

分组的关键字：group by 

 数据分组应用场景：每个部门的平均薪资，男女比例等

1.按部门分组 
select * from emp group by post;  # 分组后取出的是每个组的第一条数据
select id,name,sex from emp group by post;  # 验证
"""
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取
"""
set global sql_mode="strict_trans_tables,only_full_group_by";
 2.获取每个部门的最高工资  
以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
每个部门的最高工资
 
select post,max(salary) from emp group by post;
补充:在显示的时候还可以给字段取别名
 
select post as '部门',max(salary) as '最高工资' from emp group by post;
as也可以省略 但是不推荐省 因为寓意不明确
 
每个部门的最低工资
select post,min(salary) from emp group by post;
 
每个部门的平均工资
select post,avg(salary) from emp group by post;
 
每个部门的工资总和
select post,sum(salary) from emp group by post;
 
每个部门的人数
select post,count(id) from emp group by post;
 
统计的时候只要是非空字段 效果都是一致的 
这里显示age，salary，id最后演示特殊情况post_comment

分组补充函数
group_concat  分组之后使用
如果真的需要获取分组以外的数据字段 可以使用group_concat()
# 每个部门的员工姓名
select post,group_concat(name) from emp group by post;
 
select post,group_concat(name,'|',sex) from emp group by post;
 
select post,group_concat(name,'|',sex, '|', gender) from emp group by post;
 
select post,group_concat(distinct name) from emp group by post;
 
select post,group_concat(distinct name separator '%') from emp group by post;
concat  不分组使用
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;
 
# concat_ws()
select post,concat_ws('|', name, age, gender) from emp group by post;
 

关键字之having过滤
where与having都是筛选功能 但是有区别
    where在分组之前对数据进行筛选
    having在分组之后对数据进行筛选

 1.统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.

# 先筛选出年龄在30岁以上的
select * from emp where age > 30;
 
# 在进行分组，按照部门分组
select avg(salary) as avg_salary from emp where age > 30 group by post;
 
# 保留平均薪资大于10000的部门
select avg(salary) as avg_salary from emp where age > 30 group by post having avg(salary) > 10000;
关键字之distinct去重
distinct:去重
 
"""带主键的数据去重有没有意义? 没有，主键本身就是唯一的"""
 
​select distinct id,age from emp;
关键字之order by排序
select * from emp order by salary; #默认升序排
select * from emp order by salary desc; #降序排

#先按照age降序排，在年轻相同的情况下再按照薪资升序排
select * from emp order by age desc,salary;

'''多字段排序，如果想让后面的字段排序生效，前提：前面的排序字段必须一样'''
 

# 统计各部门年龄在20岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

#  20岁以上的员工

select * from emp where age > 20;

# 各部门的平均薪资

select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000;

select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000 order by avg(salary) desc;

关键字之limit分页
# 限制展示条数
select * from emp limit 3;
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;

# 分页显示
select * from emp limit 0,5;  # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5; 

关键字之regexp正则
select * from emp where name regexp '^j.*(n|y)$';