Mysql常用操作

create database python if not exits; #创建数据库
create table student( name varchar(20),age int); #创建表
create table name (id int(3) auto_increment not null primary key, xm char(8),xb char(2),csny date); #有条件创建表

create table tb(id int default 0); #默认0
alter table tb add name varchar(20) default "no"; #默认no
insert into tb values(1,'fei');
insert into tb(id) values(2);
insert into tb(name) values('qing'); #指定字段插入
alter table tb add class tinyint;
insert into tb values(3,'lu',2);
insert into tb values(default,default,5); #没明确赋值，则按照默认值赋值。
create table tb3 (id int unique key,name varchar(20)); #唯一key
insert into tb3 values (1,"name2");
create table tb4(id int primary key,name varchar(20)); #主key=非空+唯一
create table tb5(id int primary key auto_increment,name varchar(20)); #auto需要和主key结合使用
insert into tb5 values (default ,"name5"); #

create table b(id_b int primary key,name varchar(20),pyhone int,foreign key(id_b) references a(id_a)); # 外key
create table sel( s_id int,cours_id int,primary key(s_id,cours_id), foreign key(s_id) references student(id),foreign key(cours_id) references cours(cours_id)); #表关系，一对，一对多，多对多

create table student(id int primary key,sex enum('M','F') ,age tinyint(4) ,name varchar(20)) ; #enum枚举
create table cours(cours_id int primary key auto_increment,cours_name varchar(20) not null);

insert into student(id) values(7); #指定字段插入
insert into studen values("feifei",18) #单行插入
insert into student values('1a2',20),('1a3',20),('1a4',20),('1a5',20); #多行插入
update name set csny='1971-01-10' where xm='张三'; #更新

select * from student; #查询
select * from student where age >=20; #条件查询
select * from student where name is NULL; #is null
select * from student where class='sen' and age=20 ; #and 都满足
select * from student where (class = 'sen' or age=20 )and sex='F' ;
select * from student where not class='sen' ; #Not
select * from student order by id desc ; #倒序 asc升序
select age,name from student order by name ; #依据name打印age和name字段
select * from student limit 3; #显示3行
select * from student limit 2, 3; #从第2行开始，连续打印3行
select * from student where (class='sen' or class='TO' )and (age=20 or age=21) order by id desc limit 5;# 联合查询
select distinct name from student; #去重
select * from student where name like 'b%'; # %匹配多个字符，不能查询NULL空值哟
select * from student where name like 'ba_'; #_只能匹配一个字符哟
select * from student where id>=2 and id <=6;
select count(age) from student; #统计行书，null值不算

select MAX(age) from student; #取最大值
select avg(age) from student; #取平均值
select sum(age) from student; #求和
select sum(age),max(age),avg(age),count(age) from student; #来张表格
select group_concat(age) from student; #分组取值
select id,group_concat(name),age,class from student group by class; #分组取值，生成表格
select class,avg(age) from student group by class; #分组取值
select name from student group by class; #group by还会去重功能哟，分组情况：只能出现聚合和分组字段，不然没意义。
select class,avg(age) from student where id <=4 group by class having class='TO'; #聚合分组筛选
select class,avg(age) as aa from student where id <=4 group by class ; # 别名
select * from student where age >(select avg(age) from student); #select子查询
select * from student join detail; #join 联合查询
select * from student join detail on student.id =detail.id; #条件联合查询
select * from student left join detail on student.id =detail.id;#左外连接，以左表为准，右表没有的填空
select * from student right join detail on student.id =detail.id; #右表连接，以右表为准，左表没有的填空

select执行顺序：首先执行where-->聚合函数，别名函数-->having筛选

alter table student rename to stud; #修改表名
alter table stud change id student_id int; #修改字段名
alter table stud modify student_id tinyint; #修改字段类型
alter table stud add phone int; #添加字段
alter table stud drop student_id tinyint; #删除字段
alter table student modify age char(20); #修改字段类型

作业1：
create table student (id int(5) auto_increment not null primary key,name varchar(32),age int(3),sex varchar(8),class varchar(8)); #有条件创建表
insert into student values(1,tony,20,M,sen) #插入
insert into student values(2,'lily',21,'M','sen');
insert into student values(3,'bai2',23,'F','MU');
insert into student values(4,'bai',22,'M','TO');
select * from student where sex ='M';#条件查询
delete from student where id=3 #删除id3
update student set sex='M' where sex='F'; #更新

作业2：
select class,count(name) as person from student group by class;
select class,count(name) as person from student where age >21 group by class;
select class,count(name) from student where class='sen';
select class,count(name) from student where class='sen' and age > 20;

delete * from image; #清空表数据
drop from image; #删除表数据和表结构
delete from image_locations where image_id ="a734ac27-12bc-44ec-905e-b5fff79533e9" ; #条件删除

mysqldump -uroot -pe107ad77a2584db36e2f4468f24eec85 -B --all-databases | gzip > all.sql.gz #备份
mysqldump -uroot -p -B --all-databases | gzip > all.sql.gz #备份
mysqldump -u root -p --opt aaa > back_aaa #备份
mysql -u root -p ccc < back_aaa #恢复

grant select,insert,update,delete ondd to guyingtao@"%" Identified by "root123"; #新建用户
grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123"; #新建用户
ALTER USER 'root'@'%' IDENTIFIED BY 'SHunicom#1033' PASSWORD EXPIRE NEVER;
alter user root@'%' identified with mysql_native_password by ‘moxi1234@’; #修改mysql 认证