SQL学习笔记2

DDL、DML、DQL、DCL基础语法

1、DDL

查询

查询所有数据库：show databases;

show databases;

查询当前数据库：select database();

select database();

数据库创建

创建数据库:create database [if not exist(若存在重名数据库，则不创建)] 数据库名 [default charset 指定字符集] [collate 排序规则]

creat database MYDATA 

数据库删除

删除指定数据库：drop database [if exist(若不存在指定数据库，则不操作)] 数据库名

数据库切换

使用指定数据库：use 数据库名;

use MYDATA

表创建

在当前数据库中创建表格：show tables;

creat table 表名(

                                字段1 字段1类型 [comment 注释1]，

                                字段2 字段2类型 [comment 注释2]，

                                ……

                                字段n 字段n类型 [comment 注释n]

                           ）[comment =表注释]；

create table my_table (
    id int comment '序号',
    name varchar(50) comment '名字',
    number varchar(30) comment '学号',
    score int comment '分数'
) comment='成绩单';

表查询

查询当前数据库所有表格：show tables;

show tables;

查询指定的表结构：desc 表名;

desc my_table;

查询指定表的建表语序：show create table 表名；

show create table my_table;

表修改

为表格添加一个字段：alter table 表名 add 字段名 类型（长度）[comment]；

alter table my_table add ranks int comment '排名';

修改某个字段的类型：alter table 表名 modify 字段名 新类型（长度）；

alter table my_table modify score double;

修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 新类型（长度）[comment]

alter table my_table change score fenshu int comment '得分';

删除指定字段：alter table表名 drop 字段名；

alter table my_table drop fenshu;

 重命名表格：alter table 表名 rename to 新表名

alter table my_table rename to mytable;

表删除

删除指定表格：drop table 表名；

drop table my_table;

删除指定表格，并重新创建该表：truncate table 表名；

truncate table mytable;

2、DML

添加数据

为指定的字段添加数据：insert into 表名 (字段1，字段2，……) value (值1，值2,……);

insert into mytable (id,name) value (1,'小明');

为所有字段添加数据，值的顺序与字段顺序一致： insert into 表名 values (值1，值2,……);

insert into mytable values (1,'小王','2301110','63');

批量为指定字段添加数据:insert into 表名 （字段1，字段2，……）value(值1，值2，……)（值1，值2，……）；

insert  into  mytable (id,name) values (3,'小红'),(4,'小王');

批量为所有字段添加数据：insert into 表名 （值1，值2，……）,（值1，值2，……）;

insert into mytable values (5,'小张','234234',88),
                           (6,'小吕','224556',35);

#注意，日期与字符串类型数据使用引号标注，插入数据的长度不超过定义的长度

修改数据

修改指定字段的指定值：update 表名 set 字段名1=值1,字段名2=值2,……[where 条件]#若不指定条件，则默认修改所有指定字段的数据

update mytable set score = 99 where name = '小吕'

update mytable set score = 78,number = '123456' where name ='小王'

删除数据

修改指定字段的该行数据（不可删除单个字段）：delete from 表名 [where 条件];#若不指定条件，则清空数据表

delete from mytable where id = 1;

3、DQL

基础查询

查询表中指定字段的数据：select 字段1，字段2，字段3…… from 表名;

select id,name from mytable;

查询表中所有字段的数据（不建议在实际中用）：select * from 表名;

select * from mytable;

查询表中字段时为字段起别名：select 字段1 [as] '别名1'，字段2 '别名2'，…… from 表名

select id as '序号',name '姓名' from mytable;

查询表中字段数据时清除重复值：select distinct 字段 from 表名；

select distinct wherefo from mytable;

条件查询

查询表中与指定的条件匹配的数据：select 字段1，字段2，字段3……from 表名 where 条件

条件包括：

>:大于               <:小于        =：等于        <=：小于等于        >=:大于等于     <>/ !=：不等于

between x and y：位于x和y之间的数，包含x和y

in(值1，值2，……):匹配in中列表的任一值

like _:模糊匹配单个字符        like %:模糊匹配任意字符

is null：为空字符 

and/&&：与

or/|| :或

not/!：非

select id,name,wherefo,score,dates from mytable where score > 560;
select id,name,wherefo,score,dates from mytable where wherefo = '北京';
select id,name,wherefo,score,dates from mytable where score between 520 and 580;
select id,name,wherefo,score,dates from mytable where wherefo != '北京'
select id,name,wherefo,score,dates from mytable where name like '___';
select id,name,wherefo,score,dates from mytable where name like '小%';
select id,name,wherefo,score,dates from mytable where id<9 and wherefo = '上海';

分组查询

聚合函数，将表中的一列作为整体纵向计算（无法统计null）：select 聚合函数（字段） from 表名；

聚合函数包括：

count:统计数量

max:最大值

min:最小值

avg:平均值

sum：求和

select count(*) from mytable;
select count(number) from mytable;
select sum(score) from mytable;
select avg(score) from mytable;
select max(score) from mytable;
select min(score) from mytable;
select count(distinct wherefo) from mytable;
select count(*) from mytable where wherefo = '上海';

根据分组后的条件再进行分组过滤：select 字段 from 表名 [where 条件] group by 分组字段名 [having 过滤后条件]

where条件发生在分组前，且无法对聚合函数进行判断

having条件发生在分组后，可以对聚合函数进行判断

select wherefo as '城市',count(*) as '人数' from  mytable group by wherefo;
select wherefo as '城市',avg(score) as '平均分' from  mytable group by wherefo;
select wherefo as '城市', avg(score) as '平均分' from mytable group by wherefo having avg(score) > 560 and count( *)>3;

排序查询

根据指定的字段升序、降序排序（不写排序规则则默认升序）：select 字段 from 表名 order by 字段1 排序规则1，字段2 排序规则2#在字段1值一致时看字段2

select id,name,wherefo,score,dates from mytable order by score;
select id,name,wherefo,score,dates from mytable order by score desc;
select id,name,wherefo,score,dates from mytable order by score asc,dates desc;

分页查询

根据指定的索引和每页展示的数据个数进行查询：select 字段 from 表名 limit 起始索引 每页查询数；

起始索引=（页码数-1）*每页查询数，若是第一页可以不写起始索引

select id,name,wherefo,score,dates from mytable limit 3;
select id,name,wherefo,score,dates from mytable limit 3, 3;

DQL的编写顺序：select 字段 from 表名 where 条件 group by 分组字段 having 分组后字段 order by 排序规则 limit 起始索引，每页查询数

DQL的执行顺序：from where group by having select order by limit

4、DCL

管理用户

查询用户：select * user;

创建用户：create user '用户名‘@’主机名' identified by '密码'，主机名可使用通配符%代表可以在任意主机登陆

create user 'user1'@'localhost' identified by '123456';

修改用户密码：alter user '用户名‘@’主机名' identified with mysql_native_password by '新密码'

alter user 'user1'@'localhost' identified by '654321';

删除用户：drop user ’用户名‘@’主机名‘

drop user 'user1'@'localhost';

权限管理

MYSQL常用权限（多个权限用,分隔）：

ALL/all privileges:所有权限

select:查询数据

insert:插入数据

update:修改数据

delete:删除数据

alter:修改表

drop:删除数据库/表/视图

create:创建数据库/表

*：通配符，代表所有

查询用户权限：show grants for '用户名'@‘主机名’；

show grants for 'user1'@'localhost';

授予用户权限:grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'；

grant select on my_database.mytable to 'user1'@'localhost';
grant all on my_database.* to 'user1'@'localhost';
grant all on *.* to 'user1'@'localhost';

撤销用户权限：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'

revoke insert on my_database.mytable from 'user1'@'localhost';
revoke all on *.* from 'user1'@'localhost';