数据库

#操作数据库
    #增：create database db1;
    #查：show databases;
    #改：alter database db1 charset latin1;
    #删：drop database db1；

#操作表：
    use database;
    #增：create table t1(id int,name char);
    #查：show tables;
    #改：alter table t1 modify name char(3);
            alter table t1 change name name1 char(3); 
    #删：drop table t1;

#操作文件中的内容：
    #增：insert into t1(id,name) values(1,‘egon‘),(2,‘egon1‘),(3,‘egon2‘);
    #查：select * from t1;
    #改：update t1 set name=‘sb‘ where id=2;
    #删：delete from t1 where id=1;
    #清空表：delete from t1;
                  truncate table t1;
        

create database staff; --创建数据库

use staff; --使用数据库

create table staff_info(id int primary key auto_increment,name varchar(50),age int(3),sex enum(‘male‘,‘female‘),phone bigint(11),job varchar(11)); --创建表，enum是枚举

show tables; --展示staff数据库中的表，表名字

desc staff_info; --查看表结构，字段及类型等

insert into staff_info(id,name,age,sex,phone,job) values(1,‘Alex‘,83,‘female‘,13623457890,‘IT‘); --插入数据

--int(10)与int(11)的区别：
--int占四个字节，一个字节8位，即可以表示的个数为2的32次方，int默认是十位的长度，加入int(3),那么就前面补0直到有十位的长度，可知int(10)与int(11)只是显示长度不同而已，在计算机中都是占四个字节三十二位，运用int(M,unsigned,zerofill)中，unsigned表示正数，zerofill会在前面用0填充，int(M)中的M指最大显示宽度，最大有效显示宽度为255，且最大的位数不超过M，只用于显示，不影响存储

insert into staff_info(id,name,age,sex,phone,job) values(2,‘egon‘,26,‘male‘,12345679878,‘Teacher‘);

insert into staff_info(id,name,age,sex,phone,job) values(3,‘哪吒‘,25,‘male‘,78945645646,‘haha‘);

create table t1(id1 int,id2 int(5));
insert into t1 values(1,1) --或insert into t1(id1,id2) valuses(1,1)
insert into t1 values(111111,111111) --未报错

--创建表的三个字段分别是float，double和decimal参数表示一共显示5位，小数部分占两位，小数点不算位数
create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
insert into t2 values(1.23,1.23,1.23); --1.23,1.23,1.23
insert into t2 values(1.1234,1.234,1.234); --1.23,1.23,1.23
insert into t2 values(1.235,1.235,1.235) --1.24,1.24,1.24

--float默认是加起来不超过6位，double小数位很多，decimal(10,0)四舍五入
create table t3(id1 float,id2 double,id3 decimal)
insert into t3 values(1.235555555555,1.23555555555,1.235555555)--1.23556,1.23555555555555,1

/...date/time/datetime.../示例
create table t4(d date,t time,dt datetime);
insert into t4 values(now(),now(),now());--2019-12-20,14:29:25,2019-12-20 14:29:25
select * from t4;
insert into t4 values(‘2020-01-01‘,null,null); --正确,不加引号是错误
insert into t4 values(‘2020-1-1‘,null,null); --正确，可以不加0
insert into t4 values(‘1‘,null,null); --错误


/...timestamp示例.../
create table t5(id1 timestamp);
insert into t5 values(‘20201214123001‘);--插入时间
insert into t5 values(‘2038-01-19 11:14:08‘); --插入时间，下限为19700101080000，上限为20380119111407


/...year示例.../
create table t7(y year);
insert into t7 values(201); --报错
insert into t7 values(2019); --正常


/...datetime示例.../
create table t8(dt datetime);
insert into t8 values(‘2019-12-20 15:00:00‘);
insert into t8 values(‘2019/12/20 15+00+00‘);
insert into t8 values(‘20191220150000‘);
insert into t8 values(20191220150000);

create table t9(v varchar(4),c char(4));--无论是varchar还是char都不能超过M，但是都可以小于M，若char小于M然后在右边用空格补齐，varchar不用补齐，varchar比char更节省空间，但是效率低一些，即空间换效率
insert into t9 values(‘ab‘,‘ba‘);
concat函数：将多个字符串连接成一个字符串,如果有任何一个参数为null,返回值为null
    select concat(id,name,score) as info from tt2;
    select concat(id,‘,‘,name,‘,‘,score) as info from tt2;--加一个逗号分隔符,相当于select concat_ws(‘,‘,id,name,score) as info from tt2;
    select name,group_concat(id order by id desc separator ‘-‘) --分组并排展示

https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc

create table t10(name char(20),gender enum(‘female‘,‘male‘));
insert into t10 values(‘哪吒‘,male‘);
insert into t10 values(‘哪吒‘,‘male,female‘); --错误

create table t11(name char(20),hobby set(‘抽烟‘,‘喝酒‘,‘烫头‘,‘翻车‘));
insert into t11 values(‘yuan‘,‘烫头,喝酒,烫头‘); --正确
insert into t11 values(‘抽烟‘,‘蹦迪‘); --错误

#增：
insert into 表名 values(值1,值2,值3...);
insert into 表名(字段1,字段2,字段3...,) values(值1,值2,值3...);
insert into 表名 values(值1,值2,值3),(值4,值5,值6),(值7,值8,值9);

#更新：
update 表名 set 字段1 = 值1,字段2 = 值2 where condition;

create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum(‘male‘,‘female‘) not null default ‘male‘, #大部分是男的
age int(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 employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
(‘egon‘,‘male‘,18,‘20170301‘,‘老男孩驻沙河办事处外交大使‘,7300.33,401,1), #以下是教学部
(‘alex‘,‘male‘,78,‘20150302‘,‘teacher‘,1000000.31,401,1),
(‘wupeiqi‘,‘male‘,81,‘20130305‘,‘teacher‘,8300,401,1),
(‘yuanhao‘,‘male‘,73,‘20140701‘,‘teacher‘,3500,401,1),
(‘liwenzhou‘,‘male‘,28,‘20121101‘,‘teacher‘,2100,401,1),
(‘jingliyang‘,‘female‘,18,‘20110211‘,‘teacher‘,9000,401,1),
(‘jinxin‘,‘male‘,18,‘19000301‘,‘teacher‘,30000,401,1),
(‘成龙‘,‘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)
;

select distinct 字段1,字段2...from 表名 where 条件 group by . .having..order by..limit()

select concat(‘姓名：‘,emp_name,‘年薪：‘,salary*12) as annual_salary from employee; --将其拼接起来


select concat_ws(‘:‘,emp_name,salary*12) as annual_salary from employee;

select post,group_concat(emp_name) from employee group by post; --按照岗位分组，并查看组内成员名

select (case when emp_name = ‘jingliyang‘ then emp_name when emp_name = ‘alex‘ then concat(emp_name,‘_bigsb‘) else concat(emp_name,‘sb‘) end) as new_name from employee;

select emp_name,post_comment from employee where post_comment is null;

select emp_name,salary from employee where salary not in (3000,3500,4000,9000);

select * from employee where emp_name like ‘eg%‘;

select post,group_concat(emp_name) from employee group by post; --按照岗位分组，并查看组内成员名
select post,count(id) as count from employee group by post; --按照岗位分组，并查看每组有多少人

select * from employee order by age,salary desc; --先按age排序，默认是升序，如果年纪相同，则按照薪资排序，此处是降序

select * from employee order by salary desc limit 0,5; --从0的下一条开始提取，提取五条

select * from employee,department; --例如employee：a,b;department:1,2，结果：a1,a2,b1,b2；employee和department换顺序：1a,1b,2a,2b

select id,name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);

select name from employee where dep_id in (select id from department where name=‘技术’);

select name from department where id not in (select distinct dep_id from employee);

select name,age from emp where age>(select avg(age) from emp);

select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

select * from employee where exists (select id from department where id = 200);

select * from employee where exists (select id from department where id =204);