SQL语句

1、mysql数据类型

1.1、数值类型

整数：tinyint、smallint、mediumint、int、bigint

浮点数: float、double、real、decimal

整型	含义（有符号）
tinyint	1个字节范围(-128~127)
smallint	2个字节范围(-32768~32767)
mediumint	3个字节范围(-8388608~8388607)
int	4个字节范围(-2147483648~2147483647)
bigint	8个字节范围(±9.22*10的18次方)

浮点型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

定点数	含义
decimal(m,d)	参数m<65 是总个数，d<30且 d<m 是小数位。

1.2、日期类型

date、time、datetime、timestamp、year

日期	含义（有符号）
date	3字节 2020-09-21
time	3字节 17：47：30
datetime	8字节 2020-0-21 17：47：30
timestamp	4字节自动存储记录修改时间
year	1字节 2020

1.3、字符串类型

字符串: char、varchar
文本: tinytext、text、mediumtext、longtext

二进制：tinyblob、blob、mediumblob、longblob

字符串类型	含义
char	0-255字节定长字符串
varchar	0-65535字节变长字符串
tinytext	0-255字节短文本字符串
text	0-65535字节长文本数据
mediumtext	0-16 777 215字节中等长度文本数据
longtext	0-4 294 967 295字节极大文本数据
tinyblob	0-255字节不超过255个字符的二进制字符串
blob	0-65535字节二进制形式的长文本数据
mediumblob	0-16 777 215字节二进制形式的中等长度文本数据
longblob	0-4 294 967 295字节极大文本数据

2、建表

create table 表名(
    列名 数据类型[约束],
    列名 数据类型[约束],
    ...

)[charset=utf8];

create table student(
    name varchar(10),
    id varchar(10),
    score int
)charset=utf8;

3、增

insert into 表名(列1，列2，...) values(值1，值2，...);
# 注意 列名与values里的值要一一对应（个数、顺序、类型）
insert into student(name,id,score) value('Jone','1234567890',120);

4、查

show databases；
SHOW TABLES;
use databas_name;# 切换到某数据库
DESC wos_author;# 查看表结构

# 基本查询
SELECT DISTINCT first_name FROM wos_author;
SELECT doi,unique_id FROM wos_document; 
select doi,first_author from wos_document order by doi asc; # 升序
select doi,first_author from wos_document order by doi desc; # 降序
select doi,first_author from wos_document order by first_author desc,doi asc; # 以first_author降序，当first_author相同时，按照doi升序
select first_author,author_email from wos_document where doi='10.1007/s12583-015-0621-0';
select first_author,author_email from wos_document where doi='10.1007/s12583-015-0621-0' and pub_year=2016;
select first_author,author_email from wos_document where doi='10.1007/s12583-015-0621-0' or pub_year=2017;
select first_author,author_email from wos_document where not pub_year=2017;
select first_author,author_email from wos_document where not pub_year!=2017 and 2018;
select first_author,author_email from wos_document where not pub_year<>2017 and 2018; # <>也是不等于的意思
select first_author,author_email from wos_document where not pub_year between 2016 and 2017;
select * from wos_document where doi is NULL;
select * from wos_document where doi is not NULL;

 # 枚举查询
select first_author,author_email from wos_document where pub_year in (2016,2017,2018);

# 模糊查询 like 'L_' -> 单个任意字符 like 'L%' -> 任意长度字符 
select first_author,author_email from wos_document where first_author like 'l%';
select first_author,author_email from wos_document where first_author like 'l____'; # first_name 是 lxxxx

# 分支结构查询
select first_author,author_email,
case 
    when cited_times>= 15 then 'A'
    when cited_times>=10 and cited_times<15 then 'B'
    when cited_times>=5 and cited_times<10 then 'C'
    else 'E'
end as 'hot_level'
from wos_document;

# 时间查询
select sysdate(); # 年月日时分秒
select curdate(); # 年月日
select curtime(); # 时分秒
select week(sysdate()); # 查询当前日期是本年的第几周
select week('2020-09-20');
select year('2020-09-20');# 查询当前日期的年份
select hour(curtime());# 查询当前日期的时
select minute(curtime());# 查询当前日期的分
select datediff('2020-09-20','2020-01-01');# 查询两个日期之间的相隔天数
select adddate('2020-09-20',20);# 查询2020-09-20后20天的日期

# 字符串查询 注意：在mysql中字符串字符的下标从1开始
select concat(str1,str2,...)  # 将多个字符串连接起来
select concat('hello',' ','mysql'); # hello mysql
select insert(str,pos,len,newStr) # 将str中指定pos位置开始len长度内容替换为newStr
select insert('hello',1,5,'mysql'); # mysql
select lower(str) #将指定字符串换为小写
select upper(str) #将指定字符串换位大写
select substring(str,num,len) # 将str字符串指定num位置开始截取len个内容
select substring('hello mysql',7,5); # mysql

# 聚合函数 对单列数据做统计的操作
select sum(cited_times) from wos_document; # 对文章引用数求和
select avg(cited_times) from wos_document; # 对文章引用数求平均值
select max(cited_times) from wos_document; 
select min(cited_times) from wos_document; 
select count(cited_times) from wos_document; # 统计引用数不为NULL的文章数目（或者说是统计行数）

# 分组查询
select first_author,sum(cited_times) from wos_document group by first_author; # 求第一作者的文章被引用的次数总数

# 分组过滤查询 对分组后的数据再操作
 select first_author,sum(cited_times) as cited_count from wos_document group by first_author having first_author like 'l%'; # 查询第一作者名字首字母为l的作者的文章被引用总数

# 限定查询
select * from wos_document limit 0,10; # 查询前10行的数据，行号从0开始编号

# 查询总结
select 列名 from 表名 where 条件 group by 分组 having 过滤 order by 排序列 limit 行号，条数

# 子查询
select 列名 from 表名 where 条件(子查询结果);
select 列名 from (子查询的结果集) where 条件；
# 注意 子查询作为临时表，必须为其赋予一个临时表名
 select doi,cited_times from (select doi,first_author,cited_times from wos_document) as temp where cited_times > 15;

# 联合查询
# 注意这里 合并的是两张表，列数必须相同，列的数据类型可以不同
select * from 表名1 union select * from 表名2； # 合并的是两个结果的合***去除重复的内容
select first_author from wos_document union select concat(first_name,' ',last_name) from wos_author;
select * from 表名1 union all select * from 表名2；# 合并的是两个结果的合***去除重复的内容，保留重复的内容


#表连接查询
select 列名 from 表1 连接方式 表2 on 连接条件；

# 内连接查询 连接方式 inner join 表名 on 条件
select first_name,last_name from wos_author inner join wos_document on wos_author.document_unique_id=wos_document.unique_id limit 0,10;
# 查两表中对应文章id的前十个文章的第一作者的名字和姓氏

# 左外连接查询 left join 表名 on 条件
# 注意： 左外连接，以左表为主表，依次向右匹配，匹配到，返回结果；匹配不到，则返回null值填充
select wos_author.first_name,wos_author.last_name,wos_affiliation.address from wos_affiliation left join wos_author on wos_author.author_id=wos_affiliation.author_id limit 0,20;
# 右外连接查询
# 注意：右外连接，是以右表为主表，依次向左匹配，匹配到，返回结果;匹配不到，则返回null值填充
select address from wos_affiliation right join wos_author on wos_author.author_id=wos_affiliation.author_id limit 0,20;

内连接查询、左外连接查询、右外连接查询小结

内连接作用：找两表中符合条件的共有的内容
左外连接作用：以左表记录为主，查与被连接的右表中与左表匹配的内容，与左表不匹配的内容都为NULL
右外连接作用：以右表记录为主，查左右两表的匹配的内容，与右边不匹配的内容都为NULL

5、改

update student set score=150 where name='Jone';
update student set name='Li ming',id='1000101100' where score=150;
# 注意：SET后多个列名=值，绝大多数情况下都要加where条件，指定修改，否则为整表更新

# 数据表的操作
alter table student add age int;# 增加age这一列
alter table student modify age varchar(3);# 修改age这列的数据类型，修改时要写全 列的名字、数据类型、约束
alter table student drop age; # 删除age这一列。删除列时，每次只能删除一列
alter table student change age year int; # 注意修改表的某列时，要写全 列的名字、数据类型、约束
alter table student rename stu;# 修改表名

6、删

delete from student where name='Li ming';
# 注意：删除时，如果不加where条件，删除的是整张表的数据

truncate table student; # 清空整张表
drop table stu; # 删除整张表

7、约束

7.1、实体完整性约束

表中的一行数据代表一个实体，实体完整性的作用是标识每一行数据不重复、实体唯一。

7.1.1、主键约束

primary key 唯一标识表中的一行数据，此列的值不可重复，且不能为null

create table student(
    unique_id int primary key,
    name varchar(10),
    id varchar(10),
    score int
)charset=utf8;

7.1.2、唯一约束

unique 唯一标识表中的一行数据，不可以重复，可以为NULL

create table student(
    unique_id int primary key,
    name varchar(10),
    id varchar(10) unique,
    score int
)charset=utf8;

7.1.3、自动增长列

auto_increment自动增长，给主键数值列添加自动增长。从1开始。不能单独使用，和主键配合。

create table student(
    unique_id int primary key auto_increment,
    name varchar(10),
    id varchar(10) unique,
    score int
)charset=utf8;

insert into student(name,id,score) values('Li ming','1111121111',120);
insert into student(name,id,score) values('Zhang hua','1111121112',122);

    mysql> select * from student;
+-----------+-----------+------------+-------+
| unique_id | name      | id         | score |
+-----------+-----------+------------+-------+
|         1 | Li ming   | 1111121111 |   120 |
|         2 | Zhang hua | 1111121112 |   122 |
+-----------+-----------+------------+-------+

7.2、域完整性约束

限制列的单元格的数据正确性

7.2.1、非空约束、默认值约束

not null 非空，此列必须有值
default值 为列复制默认值，当新增数据不指定值时，书写default，以指定的默认值进行填充

create table major(
    major_id int primary key auto_increment,
    major_name varchar(10) unique not null,
    major_hours int default 20
)charset=utf8;
insert into major(major_name) values('python');
insert into major(major_name) values('c++');

mysql> desc major;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| major_id    | int         | NO   | PRI | NULL    | auto_increment |
| major_name  | varchar(10) | NO   | UNI | NULL    |                |
| major_hours | int         | YES  |     | 20      |                |
+-------------+-------------+------+-----+---------+----------------+

mysql> select * from major;
+----------+------------+-------------+
| major_id | major_name | major_hours |
+----------+------------+-------------+
|        1 | python     |          20 |
|        2 | c++        |          20 |
+----------+------------+-------------+

7.2.2、引用完整性约束

语法：constraint 引用名 foreign key (列名) references 被引用表名(列名)
详解:foreign key 引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值

create table course(
    id int primary key auto_increment,
    name varchar(10) not null,
    num int not null,
    constraint fk_course_num foreign key(num)  references major(major_id)
)charset=utf8;
# 注意： 创建时，先创建主表，再创建从表；删除的时候先删除从表 再 删除 主表
insert into course(name,num) values('python',1);
insert into course(name,num) values('c++',2);

mysql> desc course;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
| num   | int         | NO   | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

mysql> select * from course;
+----+--------+-----+
| id | name   | num |
+----+--------+-----+
|  1 | python |   1 |
|  2 | c++    |   2 |
+----+--------+-----+
2 rows in set (0.00 sec)

8、事务

事务是一个原子操作。是一个最小的执行单元。可以由一个或者多个sql语句组成，在同一事务中，所有的sql语句都成功执行时，整个事务成功；有一个sql语句执行失败，整个事务都执行失败。

8.1、事务的边界

开始：start transaction;或者begin;
结束：
- 提交：
  - a. 显示提交：commit
  - b.隐式提交：一条创建、删除语句，正常退出（客户端连接退出）；
- 回滚：
  - a. 显式回滚：rollback
  - b. 隐式回滚：非正常退出（断电、宕机），执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。

8.2、事务的原理

数据库都会为每一个客户端都维护一个空间独立的缓存区（回滚段），一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有sql语句均正常结束，才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚。

8.3、事务的特性

Atomicity（原子性）：表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败
Consistency（一致性）：表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前的状态
Isolation（隔离性）：事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
Durability（持久性）：持久性事务完成之后，它对于系统的影响是持久的。

8.4、事务的应用

select * from account;
+----+-------+
| id | money |
+----+-------+
|  1 |  1200 |
|  2 |   200 |
+----+-------+
start transaction; # 开启事务
update account set money=money+100 where id=2;
update account set money=money-100 where id=1;
commit; # 提交事务
# 若出现执行错误
rollback; # 会恢复事务之前的状态，前提是执行出错。

9、权限管理

9.1、创建用户

use test; # 先选用test数据库
create user zhang identified by '123456';# 创建一个用户zhang，密码123456

9.2、授权

grant all on test.* to zhang;

9.3、撤销授权

revoke all on test.* from zhang;

9.4、删除用户

drop user zhang;

10、视图

10.1、视图的概念

视图是一张虚拟表，是从一个表或者多个表中查询出来的表，作用和真实的表是一样的。它包含了一系列带有行和列的数据。视图中，用户可以使用select语句查询数据，也可以使用insert、update、delete修改记录。视图可以使我们方便操作数据的同时，保障数据库的系统安全。

10.2、视图的特点

优点
- 简单化，数据所见即所得
- 安全性，用户只能查询或修改他们所能见到得到的数据
- 逻辑独立性，可以屏蔽真实表结构变化带来的影响
缺点
- 性能相对较差，简单的查询也会变得稍显复杂
- 修改不方便，特别是复杂的聚合视图基本无法修改

10.3、创建并使用视图

create view temp as select unique_id,name from student;
select name from temp;
select name from temp where unique_id=1;

10.4、视图的修改

方法1：

  create or replace view temp as select name from student;

方法2：

  alter view temp as select unique_id,name from student;

10.5、视图的删除

drop view temp;

10.6、使用视图的注意事项

注意！
- 视图不会独立存储数据，原表发生改变，视图也会发生改变，没有优化任何查询性能。
- 如果视图包含以下结构中的一种，则视图不可更新
  - 聚合函数的结果
  - distinct去重后的结果
  - group by分组后的结果
  - having过滤后的结果
  - union、union all联合后的结果

11、SQL语言分类

数据查询语言DQL：select、where、order by、group by、having
数据定义语言DDL：create、alter、drop
数据操作语言DML：insert、update、delete
事务处理语言TPL(transaction process language)：commit、rollback
数据控制语言DCL：grant、revoke

sql学习