mysql DQL DDL DML DCL详解

【DDL语句：create drop alter】

数据库
创建
(1)如果名为testdb的数据库不存在，则创建

create  database if not exists  testdb;

(2)如果名为testdb的数据库不存在，则创建；并设置其字符集为utf8mb4

create  database if not exists  testdb  default  character set  utf8mb4;

查看
(1)查看所有已经存在的数据库

show databases;

(2)查看对应数据库

show create database testdb;

(3)查看可用的字符集

show character set;

(4)查看排序方式

show  collation;

修改
(1)查看字符集，如与期望不符，则修改

show  variables like 'character%';
alter database testdb character set utf8;

(2)修改字符集，并设置为默认字符集，之后创建的表都会继承此字符集

alter database testdb default character  set utf8;

删除

drop database if exists  testdb;

2表
创建
(1)创建单一主键

create  table if not exists test1  (id int(11),name varchar(30) not null,primary key(id));

(2)创建联合主键

create table if not exists test2 (id int ,name varchar(30) not null ,primary key(id,name));

(3)设置枚举，主键，字段自增，索引

create table students (id  int primary key auto_increment,name varchar(40) not null,age tinyint unsigned,gender enum('F','M') default 'M',index(name));

查看
(1)查看当前数据库上的所有表

show tables;

(2)查看当前数据库中所有表的具体属性信息

show  table status\G;

(3)查看当前数据库中某张表的状态，使用like匹配

show  table status like  'test1' \G;

(4)查看名字以db开头的表

show  table status where name like 'db%' \G;

(5)查看名字包含db的表 （%db% 模糊匹配）

show  table status where name like '%db%' \G;

(6)查看表结构

desc students;

(7)查看某张表被创建时对应的sql语句

show create table students;

删除
(1)删除单张表

drop table if exists test1;

(2)删除多张表 用逗号隔开

drop table if exists test1,test2,test3,test4;

(3)删除主键

alter table test1 drop primart key；

(4)删除行

alter  table test1 drop age;

修改
(1)添加一个新的字段age到原有name字段的后面[指定添加的位置]

alter  table  test1 add  column age  int after name;

(2)在原有的表中添加新的字段，并为新的字段设定相应的约束

alter  table test1 add age  int  not null default 0;

(3)删除表中的某个字段

alter table  teat1 drop age;

(4)修改表中字段使用 modify 不能修改名称，只能修改字段类型

alter table test1 modify age int;

(5)修改表中字段使用change，即能改名也能修改字段类型

alter table  test1  change name name_stu int(20);

【表的DML语句：insert delete updata 】

向表中插入一条数据
(1)插入单条数据

insert into  test1 (name,age) values('jerry',23);

(2)插入多条数据

insert into test1 (name ,age) valuse('alice',18),('alice',28),('bob',25);

(3)插入数据直接赋值法

insert into  test1 set id=2,name="xiaoming",age=19,gender='M';

更新数据 修改指定字段 [字符串要加引号，数值不需要]
(1)更新表中所有行的age字段的值为28

update test1  set age=28;

(2)限定范围 更新表中id号为13的行中name的字段值为avril

update test1 set name='avril' where id=13;

(3)更新多个字段

update test1  set name='avril' ,age=23  where id=13;

删除
(1)删除表中的所有数据，清空表

delete from test1;

(2)根据指定的条件删除数据

delete from test1 where age=22;
delete from test1 where name rlike'^t.*';

表的DQL语句：【select 单表查询，多表查询，分组与聚合】

查询语句
select
（1）表中查询出所有数据，只显示前3行

select * from  test1 limit 3;

(2)表中查询age=25的行的name,age字段

select name ,age from test1 where age=25;

(3)表中查询age不等于28的数据

select * from test1 where age != 28;
select * from test1 where age <> 28;

(4)查询表中age大于等于25并且小于等于28的字段

select  * from test1 where age >=25 and age<=28;
select * from test1 where age between 25 and 28;

(5)查询age等于25或者28的数据

select * from test1 where age in (25,28);
select * from test1 where age = 25 or age =28;

(6)查询age不在25到28之间的数据

select * from test1 where age not between 25 and 28;
select * from test1 where age <25  or age > 28;

使用 like 结合通配符进行模糊查询
（1）查询表中name以j开头的数据

select *from test1 where name like 'j%';
select * from test1 where name rlike '^t.*';

(2)查询表中name字段以t开头，且只有三个字符的数据

select * from test1 where name like 't__';

使用order by 进行排序 asc 升序 desc降序

(1)select  * from test1 order by age ace;
(2) select * from test2 order by age desc ;

查询年龄最大的前100个

select name ,age from test1 order by age desc limit 100;

使用in关键字指定列表

(1)表中查找出age等于22、23、24或者25中的任意一个行的所有数据

select * from test1 where age in (22,23,24,25);

(2）表中查找出age不等于22、23、24或者25中的任意一个行的所有数据

select * from test1 where age not in (22,23,24,25);

使用distinct关键字表示去重查询 ；查询学生的年龄并去重显示年龄

select distinct age  from  test1;

使用别名，以便显示为我们指定的列名

select name as stuname,age from test1;

分组与聚合 【分组的目的是为了聚合】

（1）以性别分组，求出分组后的年龄之和

select gender, sum(age) from test1 group by gender;

（2）查询test1表，以classid分组，显示平均年龄大于25的classid

select classid,avg(age) as avgage from test1 group by classid  having avgage >25;

(3)查询test1表中，以性别分组，显示各组中，年龄大于19的学员的年龄总和

select gender ,sum(age) from test1 where  age > 19 group by gender;

(4) union 自己可去重

1)select *from t2 union select * from t2;
2)select distinct * from t2;

(5) union all 没有去重的功能，只是连接

select  *from t2 union all select * from t2;

(6) cross join

select *from students cross join teachers;

(7)cross join 两张表指定字段要加前缀，指名是谁的字段，也可加别名

select students.name ,students.age ,students.gender,teachers.name,teachers.age from students cross join teachers; 

对表起别名

select s.name ,s.age  ,s.gender ,t.name,t.age  from students s cross join teachers t;

(8)内连接 A inner join B on 条件

select *from students inner join teachers on students.teacherid=teachers.tid;
select *from students ,teachers where students.teacherid=teachers.tid;
select *from students s inner join teachers t on s.teacherid=t.tid and s.age >30;
select *from students s inner join teachers t on s.teacherid=t.tid where s.age > 30;

(9)外连接
左外连接 A left outer join B on 条件

select *from students s left outer join teachers t on s.teacherid=t.tid;
select * from students s left outer join teachers t on s.teacherid=t.tid where tid is null;

右外连接

select *from students s right outer join teachers t on s.teacherid=t.tid;
select *from students s right outer join teachers t on s.teacherid=t.tid where teacherid is null;

完全外连接 于cross join 不同

  select *from students s left outer join teachers t on s.teacherid=t.tid
  union
  select *from students s right outer join teachers t on s.teacherid=t.tid;