数据库基础（四）

MySQL语句精简

(表名、字段数据等直接举例表示)

查看所有表记录：select * from t1;

增加数据：insert into t1 values(1,'kk',20);

insert into t1 values(1,'kk'),(1,'kk'),(1,'kk');

insert into t1(id,name) values(1,'kk');

修改：update t1 set name='yt' where id = 1;

update t1 set age=30 where name = 'tt';

update t1 set name='kk',age=20 where id =1;

update t1 set name='kk' where  name='jj' and age =10;

update t1 set name='kk' where  name='jj' or age =10;

update t1 set name='kk';

update t1 set age=age+10 where id =1;

删除：delete from t1 where id=1;

delete from t1 where id =1 or id =2;

delete from t1;

配置命令(临时修改)：set global sql_mode='strict_trans_tables'; #设置严格模式

set global sql_mode='pad_char_to_full_length'; #为char类型填充长度

set global sql_mode=only_full_group_by; #分组之后默认只能够直接过去到分组的依据 其他数据都不能直接获取 

查看模式：show variables like '%mode%'

设置存储引擎：create table t1 (id int,name varchar(64),age int) engine=MyISAM;

create table t1 (id int,name varchar(64),age int) engine=InnoDB;

create table t1 (id int,name varchar(64),age int) engine=MEMORY;

数据类型：create table t1 (salary tinyint);

create table t1 (salary smallint);

create table t1 (salary int);

create table t1 (salary bigint);

#默认带符号，范围减半，

去掉符号：create table t1(salary tinyint unsigned);

create table t7 (id float(255, 30));
create table t8 (id double(255, 30));
create table t9 (id decimal(65, 30));

#decimal精度最高，一般选用decimal

create table t1 (name char(4));  #严格模式下超出4位报错，不够4位，默认不对char类型填充

create table t1 (name varchar(4));

查看数据字段长度：select char_length(name) from t1;

create table t1 (time1 date);  #年月日  #2023-10-1

create table t1 (time1 datetime);  #年月日时分秒  #2023-11-11 11:11:11

create table t1 (time1 time);  #时分秒  #11:11:11

create table t1 (time1 year);  #年  #2023

create table t1 (id int,hobby enum('唱','跳','wrap'));  #多选一

insert into t1 values(1,'wrap');

create table t1 (id int,hobby set('唱','跳','wrap'));  #多选多

insert into t1 values(1,'唱','跳','wrap');

约束条件：零填充：create table t1 (id int(4) unsigned zerofill) #不足4位用0填充

默认值：create table t1(id int ,name varchar(23) default 'kkkk');

insert into t1 values(1,'jjjj');  #可以修改默认值

insert into t1(id) values(1);

非空：create table t5 (id int, name varchar(32) not null);
insert into t5(id) values(1); #报错

唯一：create table t1 (id int ,name varchar(32) unique);

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

主键：create table t1(id int primary key);

自增（配合主键使用）：create table t1 (id int primary key auto_increment);

清空表：truncate t1;

修改表名：alter table t1 rename t2;

增加字段：alter table t1 add age int unsigned;

alter table t1 add age int unsigned first;

alter table t1 add age int unsigned after id;

删除字段：alter table t1 drop age;

修改字段：alter table t1 modify age int zerofill;  #modify只改字段数据类型完整约束

alter table t1 change t1 age age1 int zerofill;  #change还可以改字段名

关键字where：select id,name from t1 where id between 3 and 5;

select * from t1 where salary in (100,200);

关键字like：select * from t1 where name like '%k%' ;               

关键符号：%：匹配任意个数任意字符

_:匹配单个任意字符

select * from t1 where name like '_';

select name,post from emp where post_comment is NULL;

group by分组：select * from emp group by post;

聚合函数：max min avg count sum        

select max(salary) from t1 group by post ;

select min(salary) from t1 group by post ;

select avg(salary) from t1 group by post ;

select count(id) from t1 group by post ;

获取分组以外的字段group_concat：select group_concat(name) from t1 group by post;

不分组使用：select concat(name,sex) from t1;

select concat(name,'|',sex) from t1;

# concat_ws()
select post,concat_ws('|', name, age, gender) from emp group by post;

having在分组后筛选：select avg(salary) as avg_salary from emp where age > 30 group by post having avg(salary) > 10000;

distinct去重：select distinct age from t1;

order by排序：select * from t1 order by salary; #默认升序

select * from t1 order by salary desc; #降序

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

limit分页：select * from emp limit 2;

select * from emp limit 2,3;

select * from emp order by salary desc limit 1;

建立一对多表关系：        

create table t1(id int primary key auto_increment,name varchar(32),age int, t2_id int,foreign key(t2_id) references t2(id) on update cascade on delete cascade);

create table t2(id int primary key auto_increment,  dep_name varchar(32),dep_desc varchar(32));

insert into t1(name,age,t2_id) values ('kk',12,1);

insert into t2(dep_name,dep_desc) values('人事部', '管理人才');

建立多对多表关系：
create table book (id int primary key auto_increment,title varchar(32),price decimal(8,2));

create table author (id int primary key auto_increment,name varchar(32),addr varchar(32));

create table book2author(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references book(id) 
    on update cascade 
    on delete cascade, 
    foreign key(author_id) references author(id) 
    on update cascade 
    on delete cascade
);

insert into book(title, price) values('金瓶梅', 1000);
insert into book(title, price) values('西游记', 2000);
insert into author(name, addr) values('zhangsan', 'beijing');
insert into author(name, addr) values('lisi', 'shanghai');

insert into book2author(book_id, author_id) values(1, 1);
insert into book2author(book_id, author_id) values(1, 2);
insert into book2author(book_id, author_id) values(2, 1);
insert into book2author(book_id, author_id) values(2, 2);

多表查询：

子查询：select *from dep where id = (select dep_id from emp where name='kevin';);

连表查询：select * from emp inner join dep on emp.dep_id = dep.id;

select * from emp inner join dep on emp.dep_id = dep.id inner join t1 on ti.id = dep_a.id;

select * from emp left join dep on emp.dep_id =dep.id;

select * from emp right join dep on emp.dep_id =dep.id;