不同的数据库语法有差异,本文以MySQL数据库语法为例！
数据库优点：
共享/减少冗余/集中控制
一致/完整/可维护
安全/可靠/故障恢复
SQL语言分为三类:
DDL(Data Definition Language): 数据库定义语言,对数据库/表结构定义或修改,大多在建表时使用,create/alter/drop
DML(Data Manipulation Language): 数据库操作语言,对表行记录数据操作,insert/update/delete/select
DCL(Data Control Language): 数据库控制语言,用户权限管理/事务控制,grant/rollback/commit
SQL语言注释: 单行-- 多行/* */
一.数据库(DDL)
1.创建数据库
创建一个数据库(字符校对规则: 指字符集的排序规则)
create database 库名 [character set 码表名 collate 字符校对规则]
创建一个utf8字符集的数据库
create database 库名 character set utf8;
创建一个utf8字符集并带校对规则的数据库(utf8_bin 按二进制排序)
create database 库名 character set utf8 collate utf8_bin;
2.修改数据库
修改数据库 alter database 库名 character set 字符集 collate 校对集;
删除数据库 drop database 库名;
3.使用数据库
切换当前数据库 use 库名;
查看当前数据库 select database();
查看所有数据库 show databases;
查看建库语句 show create database 库名;
二.表列(DDL)
1.创建表
create table 表名(列名1 类型1, 列名2 类型2...)[character set 字符集 collate 校对规则];
create table user(
id int primary key auto_increment,
loginname varchar(10) not null unique,
password varchar(20) not null,
age int(3) not null,
birthday datetime not null
);
类型:
字符串(255B): char(n)/varchar(n)
数值: tinyint(1B)/smallint/int/bigint/float/double
逻辑: bit(0/1)
日期: Date/Time/DateTime/timestamp
大数(4GB): Blob/Text
2.修改表
增加列 alter table 表名 add 列名 类型;
删除列 alter table 表名 drop 列名;
修改列名 alter table 表名 change 列名 新列名 类型;
修改列类型 alter table 表名 modify 列名 类型;
修该表字符集 alter table 表名 character set 字符集 collate 校对集;
修改表名 rename table 表名 to 新表名;
删除表 drop table 表名;
3.查看表
查看表结构 desc 表名;
查看所有表 show tables;
查看建表语句 show create table 表名;
三.表行/记录(DML)
1.增insert
insert into 表名(列名1,列名2...) values(value1,value2...);
insert into user(name,password) values('lioil','**lioil**');
2.删delete
delete from 表名 [where ....]
1.逐行删除记录,不删除表本身
delete from user;
2.删除表本身
drop user;
3.先摧毁表,再新建表(删除效率高),不能在事务中恢复
TRUNCATE TABLE user;
3.改update
update 表名 set 列名1=value1, 列名2=value2... [where 条件1,条件2...]
update user set name='lioil' where id=1;
4.查select
SELECT selection_list /*列名*/
FROM table_list /*表名*/
WHERE condition /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*对结果排序*/
LIMIT offset_start, row_count /*结果限量*/
1).select 列名 from 表名
查找所有列 select * from 表名 (*需要运算,效率较低,最好列出列名,表结构变了后不易出错)
剔除重复行 select distinct 列名 from 表名
设置别名 select 列名1+列名2 (as) 别名 from 表名; (as可省略)
2).where
可用的运算符如下:
= != > < >= <= <>
and or not
between 1 and 10 取值在1到10之间
in(5,3,6) 取值是5或3或6
not in(1,2,3)
is null 值是否空
is not null
like 'lio%' %表示任意个字符, _表示1个字符
select * from user where name != 'li';
select * from user where not name = 'li';
select * from user where name not in('li');
select * from user where name = null; 错误(null不等于null)
select * from user where name is null; 正确
select * from user where name is not null;
select * from user where not name is null;
select * from user where name like '___'; name是三个字符
3).聚合函数(纵向运算,多行计算)
有null参与计算,结果都是null,此时可用ifnull(列名,0)处理,
如: select ifnull(列1,0) + ifnull(列2,0) from user;
count(列名) 指定列的记录个数
sum(列名) 指定列的记录值求和
avg(列名) 指定列的记录值平均值
max(列名)、min(列名) 指定列中的最大记录值和最小记录值(不是数值类型,计算结果为0)
select sum(age) from user;
select avg(age) from user;
select max(age) from user;
select min(age) from user;
select count(*) from user where age > 25;
4).group by 列名1,列名2 having...
having子句对group by结果筛选
having和where区别：
where用在分组之前, having用在分组之后, having可用sum(), where不行
5).order by 列名 asc/desc limit
asc升序(默认), desc降序
从第3行开始,查询2行记录 select * from user limit 3,2;
6).执行顺序
from join on
where
group by(从此开始可用select别名,而在此之前无法使用,如在where中不能用)
avg,sum,count等聚合函数
having
select
distinct
order by
四.约束(主键和外键)
1.创建表时指定外键约束
create table A(
id int primary key auto_increment,
fkey int,
foreign key(fkey) references B(id)
);
create table B(
id int primary key auto_increment
);
2.外键约束
增加外键 alter table 表1 add foreign key(列名) references 表2(列名) [on delete restrict] [on update cascade];
restrict: 本表外键引用了主表的记录, 在主表就无法删除相关记录
cascade: 本表和在主表会级联删除
删除外键 alter table 表名 drop foreign key 列名;
3.主键约束
增加主键 alter table 表名 add primary key(列名) auto_increment;
删除主键 alter table 表名 drop primary key
增加自动增长 alter table 表名 modify id int auto_increment;
删除自动增长 alter table 表名 modify id int;
五.多表关系和查询
一对一(教室和班级): 在任意方保存另一方主键作为外键
一对多(班级和学生): 在多方保存另一方主键作为外键
多对多(教师和学生): 中间表保存两张表主键作为两个外键,保存对应关系
ta表
+----+------+-------+
| id | name | tb_id |
+----+------+-------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | bbb | 4 |
+----+------+-------+
tb表
+----+------+
| id | name |
+----+------+
| 1 | xxx |
| 2 | yyy |
| 3 | yyy |
+----+------+
1.笛卡尔积查询
两张表记录相乘操作,左表有n条记录,右表有m条记录,最后得到m*n条记录,
select * from ta ,tb;
+----+------+-------+----+------+
| id | name | tb_id | id | name |
+----+------+-------+----+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 1 | xxx |
| 3 | bbb | 4 | 1 | xxx |
| 1 | aaa | 1 | 2 | yyy |
| 2 | bbb | 2 | 2 | yyy |
| 3 | bbb | 4 | 2 | yyy |
| 1 | aaa | 1 | 3 | yyy |
| 2 | bbb | 2 | 3 | yyy |
| 3 | bbb | 4 | 3 | yyy |
+----+------+-------+----+------+
2.内连接
查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
select * from ta inner join tb on ta.tb_id = tb.id; (可用逗号,代替inner join)
select * from ta,tb on ta.tb_id = tb.id;
+----+------+-------+----+------+
| id | name | tb_id | id | name |
+----+------+-------+----+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
+----+------+-------+----+------+
3.外连接
1).左外连接: 内连接基础上左表全部显示的结果
select * from ta left join tb on ta.tb_id = tb.id;
+----+------+-------+------+------+
| id | name | tb_id | id | name |
+----+------+-------+------+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
| 3 | bbb | 4 | NULL | NULL |
+----+------+-------+------+------+
2).右外连接: 内连接基础上右表全部显示的结果
select * from ta right join tb on ta.tb_id = tb.id;
+------+------+-------+----+------+
| id | name | tb_id | id | name |
+------+------+-------+----+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
| NULL | NULL | NULL | 3 | yyy |
+------+------+-------+----+------+
3).全外连接: 内连接基础上左表和右表都全部显示的结果
mysql不支持全外连接
select * from ta full join tb on ta.tb_id = tb.id;
mysql可用union合并两条select语句,间接实现全外连接
select * from ta left join tb on ta.tb_id = tb.id;
union
select * from ta right join tb on ta.tb_id = tb.id;
+------+------+-------+------+------+
| id | name | tb_id | id | name |
+------+------+-------+------+------+
| 1 | aaa | 1 | 1 | xxx |
| 2 | bbb | 2 | 2 | yyy |
| 3 | bbb | 4 | NULL | NULL |
| NULL | NULL | NULL | 3 | yyy |
+------+------+-------+------+------+
简书: http://www.jianshu.com/p/b4e63c010ed1
CSDN博客: http://blog.csdn.net/qq_32115439/article/details/54743585
GitHub博客：http://lioil.win/2017/01/25/SQL.html
Coding博客：http://c.lioil.win/2017/01/25/SQL.html
