Database-SQL语句基础

不同的数据库语法有差异,本文以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语言注释:  单行--  多行/* */

创建一个数据库(字符校对规则: 指字符集的排序规则)
create database 库名 [character set 码表名 collate 字符校对规则]
			
创建一个utf8字符集的数据库	
create database 库名 character set utf8;
	
创建一个utf8字符集并带校对规则的数据库(utf8_bin 按二进制排序)
create database 库名 character set utf8 collate utf8_bin; 

修改数据库   alter database 库名 character set 字符集 collate 校对集;	
删除数据库   drop database 库名;

切换当前数据库  use 库名;		
查看当前数据库  select database();		
查看所有数据库  show databases;		
查看建库语句    show create database 库名;

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

增加列        alter table 表名 add 列名 类型;
删除列        alter table 表名 drop 列名;
修改列名      alter table 表名 change 列名 新列名 类型;
修改列类型    alter table 表名 modify 列名 类型;
修该表字符集  alter table 表名 character set 字符集 collate 校对集;
修改表名      rename table 表名 to 新表名;
删除表        drop table 表名; 

查看表结构     desc 表名; 
查看所有表     show tables;
查看建表语句   show create table 表名;

insert into 表名(列名1,列名2...) values(value1,value2...);	
insert into user(name,password) values('lioil','**lioil**');

delete from 表名 [where ....]
1.逐行删除记录,不删除表本身
	delete from user;
2.删除表本身
	drop user;
3.先摧毁表,再新建表(删除效率高),不能在事务中恢复
	TRUNCATE TABLE user;

update 表名 set 列名1=value1, 列名2=value2... [where 条件1,条件2...]
update user set name='lioil' where id=1;

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

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
);

增加外键 alter table 表1 add foreign key(列名) references 表2(列名) [on delete restrict] [on update cascade];
	restrict: 本表外键引用了主表的记录, 在主表就无法删除相关记录
	cascade:  本表和在主表会级联删除		
删除外键 alter table 表名 drop foreign key 列名;

增加主键      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  |
+----+------+

两张表记录相乘操作,左表有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  |
+----+------+-------+----+------+

查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。	
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  |
+----+------+-------+----+------+

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  |
+------+------+-------+------+------+