sql总结
数据库sql语句 (结构化查询语言)
DDL(Data Definition Language)数据定义语言用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter等
一 创建 关键字 create
1. 创建数据库create database......
创建数据库 create database mydatabase；
2.创建数据库带编码create database.....character set......
带编码 create database mydatabase character set utf8;
3. 创建表create table....()......
create table mytable(
name char(10),
age int，
birthday date
);
4. 根据已有的表结构创建新表create table.....like.......
create table copymytable like mytable;
5. 创建用户 create user.... identified by.....
create user '用户名'@'主机' identified by '密码';
create user 'user1'@'localhost' identified by '123456';
二 修改 关键字 alter
1. 修改数据库编码格式alter database......default charactor set.......
alter database mydatabase defaule charactor set utf-8;
2.修改表的字符集（编码格式）alter table.......charactor set.......
alter table mytable charactor set utf-8；
3.修改表字段
1. 添加字段 alter table.....add......
alter table mytable add myline varchar(10);
2.修改字段类型alter table.....modify......
alter table mytable modify myline int
3.修改字段名字alter table.....change ......
alter table mytable change myline newmyline varchar();
4.删除一个字段alter table.....drop......
**alter table** mytable **drop** myline;
三 删除 关键字drop
1. 删除数据库 drop database......
drop database mydatabase;
2. 删除表drop table......
drop table mytable；
四 完整性约束条件primary key/unique/not null/default/poreign key/zerofill/unsigned
- primary key: 主键约束
- unique: 唯一约束 注：唯一但是可以为空(空和空不相等)
- not null: 非空约束
- default: 默认值
- poreign key: 外键约束
- zerofill :零填充
- unsigned:无符号位 注：无符号作用于数值类型
1.在创建的表时候同时创建约束
**create table **mytable(
id int primary key/unique/not null/default 6/foreign key references subject(id)/zerofill/unsigned
);
**create table **mytable(
id int ,
name varchar(10),
**primary key**(id,name)/**unique(**id,name**)/foreign key(id) references** subject(id);
);
-
增加约束
alter table mytable add primary key(id,name)/unique(id,name)/not null/default 6/zerofill;
添加外键约束
**alter table **mytable add foreign key(id) references subject(id);
带外键名的添加方法
**alter table ** mytable add constraint key_name foreign key(id) references subject(id);
在添加外键约束的时候可以设置他的级联更新和级联删除
on update cascade 级联更新，主键表的主键修改同时会修改外键表的外键
**alter table ** mytable add constraint key_name foreign key(id) references subject(id) on update cascade;
on select cascade 级联删除，主键表的数据删除，同时会删除外键表对应的数据
**alter table ** mytable add constraint key_name foreign key(id) references subject(id) on delete cascade no update cascade ;
-
删除约束
unique 约束删除用关键字 index
**alter table **mytable drop primary key/index name_2
删除外键
**alter table **mytable drop foreign key key_name;
注：主键约束相当于(唯一约束+非空约束)
一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示：
Multiple primary key defined!!!
删除主键约束前，如果有自增长需要先删除自增长,如果不删除自增长就无法删除主键约束
int类型主键自增auto_increment
**create table **mytable(
id int primary key auto_increment
);
级联 cascade
on delete cascade 删除主表中的数据时，从表中的数据随之删除
on update cascade 更新主表中的数据时，从表中的数据随之更新
**on delete set null **删除主表中的数据时，从表中的数据置为空
默认 删除主表中的数据前需先删除从表中的数据，否则主表数据不会被删除
DML(Data Manipulation Language)数据操作语言用来对数据库中表的数据进行增删改。关键字：insert, delete, update等
一 插入记录 关键字 insert into
1.插入一条记录insert into.....value......
insert into mytable(id,name,age)value(1,'张三',20);
inster into mytable value(1,'张三',20);
二 更新记录 关键字 update.....set
1. 更新字段update.....set.......
更新所有记录的id字段update.....set.......
update mytable set id=2；
按条件更新id字段update.....set.......where......
**update** mytable **set** id=2 **where** name=‘张三’,sex='女';
三 删除记录 关键字 delete from
1. 删除记录 delete from....
删除表中所有记录delete from........
delete from mytable;
按条件删除表中记录 delete from......where.......
delete from mytable where id=1;
DQL(Data Query Language) 数据查询语言对数据库进行数据查询，关键字select
一 查询 关键字 select......from
1.查询记录的 所有/部分 字段 select * ......from / select id,name......from 别名 as
属性名后面+空格+别名 也可以用as关键字
select * from mytable;
select id as 学号,name from mytable;
2.按条件查询记录的 所有/部分 字段select* ......from.......where....../select id,name ......from.......where......
select * from mytable where id=1;
select id,name from mytable where id=1;
3. where中的条件
1.逻辑运算 and/or/!
select * from mytable where id is null and name='张三' ;
select * from mytable where id is null or id is not null;
select * from mytable where name!='张三' ;
2. 判空 is null/is not null
select * from mytable where id is null;
select * from mytable where id is not null;
3. 可枚举值的范围查询 in();
select * from mytable where id in(1,2,4);
4. 连续范围查询 between.....and......
操作符 between ... and 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
select * from mytable where id between 1 and 4; --1 4都包含
5. 模糊查询 like.....
通配符
% 代表任意零个或多个字符
_ 代表任意一个字符
select * from mytable where name like '张%' ;
4. 对结果集进行排序 order by.....asc/desc
升序 asc（默认）asc可省略 但是为了可读性 建议不要省略
select * from mytable where name like '张%' order by id asc ;
降序 desc
select * from mytable order by id desc;
5. 聚合函数 max() min() avg() sum() count()......
聚合函数放在**select/having**后面 他是对查询出的所有记录的指定字段进行操作
MAX(字段) ，统计指定字段的最大值，不统计记录为null
MIN(字段) ，统计指定字段的最小值，不统计记录为null
AVG(字段) ，统计指定字段的平均值，不统计记录为null
SUM(字段) ，统计指定字段的求和，不统计记录为null
COUNT(字段)，统计指定字段的记录条数，要求当前字段的值不能为空的记录才能统计到
select max(id) from mytable ;
6. 分组查询 group by......having.......
group by可以将查询结果按照指定字段进行分组 having 可以对分组后的记录进行条件筛选 如果没有对查询结果进行聚合操作 分组默认显示查询的第一行记录
在where中不能使用别名 应为在where语句运行时select语句后的别名还没有执行所以还不存在，当需要大量使用别名的时候可以用group by....having分组筛选代替where语句
*select name** 姓名 ,english 英语,chinese+english+math sum **from **student **group by **id having sum>220 ;
select sex,avg(age)from mytable group by sex;
select sex,avg(age)from mytable group by sex having avg(age)>40;
7. 分页查询 limit
取出查询结果的一部分
limit 0,3 起始位置从0开始,如果不写默认就是0 取三个数据 第一行记录也是从0开始的 包括开头
**select ** name 姓名 from mytable where sex='男' group by age desc limit 0,3;
8.去重查询 distinct
去掉查询结果中的重复记录
select distinct name from student;
9. top字句 top/top....percent
top 子句用于规定要返回的记录的数目。
注释：并非所有的数据库系统都支持 TOP 子句。
select top 2 * from mytable;
top percent 选取 50% 的记录
select top 50 percent * from mytable;
二 多表查询....[inner] join......on...../.......left [outer] join.....on....../.......right[outer] join.....on......
1. 笛卡儿积
select * from student , subject;
用 where 语句除去笛卡儿积(隐式内链接)
select * from student , subject where student . id = subject . id;
2. 内链接....[inner] join......on.....
表与表之间连接 并用on关键字指定连接条件 inner 关键字可以省略
**select * from **student [inner] join subject on student . id = subject . id;
3. 外连接.......left [outer] join.....on....../.......right[outer] join.....on......
左/右外连接 在符合内连接的基础上让左/右表的数据全部出来，右/左边有数据就显示，无数据就是为null
找出所有学生的信息 还没来得及选课的同学 课程显示为null；
**select * from **student left [outer] join subject on student . id = subject . id;
**select * from **student right [outer] join subject on student . id = subject . id;
4. 子查询
一个查询的结果是另一个查询的一部分，说白了就是查询里面包含另一个查询
select * from student where id = (select max(id) from subject );
Data Control Language(DCL) 数据控制语言 给用户授权，撤销授权
1. 用户授权 grant/revoke
1. 授权
grant 权限1,权限2,权限3,... on 数据库名.表名 to '用户名'@'主机';
-- 常用权限介绍
create-- 创建表或数据库权限
alter-- 修改表结构权限
drop -- 删除表或数据库权限
insert-- 添加表数据权限
update-- 更新表数据权限
delete-- 删除表数据权限
select-- 查询表数据权限
all -- 所有权限
授予所有数据库所有资源
grant all on * . * to 'user1'@'localhost';
2. 撤销授权
revoke 权限1,权限2,... on 数据库名.资源名 from '用户名'@'主机';
撤销user1对db1数据库所有资源的所有权限
revoke all on db1.* from 'user1'@'localhost';
3. 查看权限
show grants for '用户名'@'主机'; -- 查看指定用户的权限，适合root用户操作
show grants; -- 查看当前用户的权限
其他语句
查看 show
1.查看数据库show databases
show databases；
2.查看指定数据库show database......
**show database **mydatabase;
3.查询表的创建代码show create table......
show create table mytable;
选择数据库 use.....
use mydatabase；
修改表的名字 rename....to
rename table mytable to newmytable；
查询指定表的定义信息 desc....
desc mytable;
摧毁表结构再重新创建表结构 truncate table.....
truncate table mytable;
常用sql语句表
| 语句 | 语法 |
|---|---|
| AND / OR | SELECT column_name(s) FROM table_name WHERE condition AND|OR condition |
| ALTER TABLE (add column) | ALTER TABLE table_name ADD column_name datatype |
| ALTER TABLE (drop column) | ALTER TABLE table_name DROP COLUMN column_name |
| AS (alias for column) | SELECT column_name AS column_alias FROM table_name |
| AS (alias for table) | SELECT column_name FROM table_name AS table_alias |
| BETWEEN | SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 |
| CREATE DATABASE | CREATE DATABASE database_name |
| CREATE INDEX | CREATE INDEX index_name ON table_name (column_name) |
| CREATE TABLE | CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... ) |
| CREATE UNIQUE INDEX | CREATE UNIQUE INDEX index_name ON table_name (column_name) |
| CREATE VIEW | CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition |
| DELETE FROM | DELETE FROM table_name (Note: Deletes the entire table!!)orDELETE FROM table_name WHERE condition |
| DROP DATABASE | DROP DATABASE database_name |
| DROP INDEX | DROP INDEX table_name.index_name |
| DROP TABLE | DROP TABLE table_name |
| GROUP BY | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 |
| HAVING | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value |
| IN | SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..) |
| INSERT INTO | INSERT INTO table_name VALUES (value1, value2,....)orINSERT INTO table_name (column_name1, column_name2,...) VALUES (value1, value2,....) |
| LIKE | SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern |
| ORDER BY | SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC] |
| SELECT | SELECT column_name(s) FROM table_name |
| SELECT * | SELECT * FROM table_name |
| SELECT DISTINCT | SELECT DISTINCT column_name(s) FROM table_name |
| SELECT INTO (used to create backup copies of tables) | SELECT * INTO new_table_name FROM original_table_nameorSELECT column_name(s) INTO new_table_name FROM original_table_name |
| TRUNCATE TABLE (deletes only the data inside the table) | TRUNCATE TABLE table_name |
| UPDATE | UPDATE table_name SET column_name=new_value [, column_name=new_value] WHERE column_name=some_value |
| WHERE | SELECT column_name(s) FROM table_name WHERE condition |
