SQL教程

Sep 4, 2020

 research  SQL

Structured Query Language : 访问和处理关系数据库的计算机标准语言

1 数据模型

  • 层次模型
  • 网状模型
  • 关系模型

2 操作数据库

  • DDL: Data Definition Language
  • DML: Data Manipulation Language
  • DQL: Data Query Language

3 安装MySQL

  • 作为一个SQL接口包含多种数据引擎
  • 解压目录下新建配置文件
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
  
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\\download\\mysql-8.0.18
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=H:\\MyWork\\mysql-8.0.11\data
# 允许最大连接数
max_connections=100
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
  • 初始化安装并加入环境变量
mysqld --initialize --console
wykdgs,WP0zc
mysqld install
net start mysql
mysql -u root -p
alter user 'root'@'localhost' identified by 'sysqxt1009';
exit;
net stop mysql
mysqld --remove
  • macOS下安装MySql
brew install mysql
mysql.server start
mysql_secure_installation
mysql -u root -p
#使用mysql密码插件
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
#卸载mysql密码插件
UNINSTALL PLUGIN validate_password;
#重置密码
vim /usr/local/etc/my.cnf
skip-grant-tables#[mysqld]下添加
mysql.server stop
mysql.server start#重开新终端
mysql -u root -p
show databases;
use mysql;
update user set authentication_string='' where user='root';
flush privileges;
show variables like 'validtate_password%';
alter user 'root'@'localhost' identified by 'ipad air4';
flush privileges;
quit;
exit;

4 关系模型

  • 记录（行）：Record
  • 字段（列）：Coloumn
  • 任意两条记录不能重复
  • 字段尽量避免允许为NULL

4.1 主键

能唯一区分出不同记录的字段，最好是完全与业务无关的字段id

  • 自增整数类型（INT/BIGINT AUTO_INCREMENT）
  • 全局唯一GUID类型

4.2 外键

可以把数据与其他表关联起来的字段，通过定义外键约束实现

  • 外键约束（FOREIGN KEY CONSTRAINT）
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id)
DROP FOREIGN KEY fk_class_id
DROP COLUMN (class_id)
  • 双鸭山气象站地面资料数据库采用一对一关系，先期建立日期降水、日期气温、日期风速三张表，避免NULL，提高查询速度。

4.3 索引

  • 因为主键能够保证绝对唯一，所以效率最高
  • 日期有缺失，但具有唯一性约束，所以将日期设为唯一索引
ALTER TABLE students
ADD INDEX idx_score(score)
ADD UNIQUE INDEX uni_name (name)
-- SELECT * FROM students;

5 查询数据

  • 对MySQL数据库的所有修改都会保存下来，再次运行脚本，恢复到初始状态。

5.1 基本查询

$ d:
$ cd documation\program\sql
$ net start mysql
$ mysql -u root -p < init-test-data.sql
$ mysql -u root -p
use test;
select * from students;
select name from classes;
select 1;

5.2 条件查询

  • 用来查询符合日期条件的各气象要素 
    select score from students where id <> 2 and gender = 'M';
not;
or;
like;
%;
between;

5.3 投影查询

5.4 排序

select id,name,gender,score 
from students 
where class_id = 1 
order by score desc,gender;

5.5 分页查询

  • limit = pageSize
  • offset = pageSize * (pageIndex - 1) 
    SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
limit 3,0;
limit 3;

5.6 聚合查询

  • 统计查询出的气象要素 
    select count(*) num from students where gender = 'M';
-- 查询行数
select sum(pre) total_pre from precipitation where (month between 6 and 8) and year = 2016;
-- 计算2016年夏季降水量
select avg(temp) avg_tmp from temperature where year between 1950 and 1980 by month；
-- 计算30年每月平均气温
max；
-- 计算某一列的最大值
min；
-- 计算某一列的最小值
  • 数据库结构日期拆分成年、月、日，便于查询，目前还差入库方法。

5.7 多表查询

  • 有多少个气象要素就有多少张表 
    select
  s.id id,
  s.name,
  s.gender,
  s.score,
  c.id cid,
  c.name cname
  from students s,classes c;

5.8 连接查询

  • 内连接：inner join 
    SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
  • 外连接：outer join 
    right outer join;
left outer join;
full outer join;

    6 修改数据

  • 增查改删，CRUD：create(insert)、retrieve(select)、update、delete

6.1 INSERT

insert info <> () values
(),
();

6.2 UPDATE

update <> set ()=() where ();

6.3 DELETE

delete from <> where ()

7 MySQL

graph LR
id1[MySQL Client] --> |SQL/TCP|id2[MySQL Server]
  • 可视化图形界面：MySQL Workbench 
    show databases;
-- information_schema
-- mysql
-- performance_schema
-- sys
create database test;
drop database test;
use test;
show tabels;
desc students;
show create table students;
drop table students;
alter table students add column birth vachar(10) not null;
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
ALTER TABLE students DROP COLUMN birthday;
  • 实用SQL语句 
    replace into students (id,class_id,name,gender,score) values (1,1,'小明','F',99)；
--插入或替换
insert into students (id,class_id,name,gender,score) values (1,1,'小明','F',99) on duplicate key update name='小明',gender='F',score=99;
--插入或更新
insert ignore into students (id,class_id,name,gender,score) values (1,1,'小明','F',99);
--插入或忽略
create table student_of_class1 select * from students where class_id=1;
--快照
create table statistics (
  id bigint not null auto_increment,
  class_id bigint not null,
  average double not null,
  primary key (id)
);
--创建表
insert into statistics (class_id,average) select class_id,AVG(score) from students group by class_id;
--写入各班平均成绩
select * from students force index (idx_class_id) where class_id = 1 order by id desc;

    8 事务

  • 数据库事务具有把多条语句作为一个整体进行操作的功能，全部成功或全部失败。
  • 特性，ACID：atomic（原子性）、consistent（一致性）、isolation（隔离性）、duration（持久性）
  • 隐式事务、显式事务 
    begin;
update accounts set balance = balance - 100 where id = 1;
update accounts set balance = balance + 100 where id = 2;
commit;
--rollback;
  • 隔离级别
isolation level dirty read nonrepeatable read phantom read
read uncommitted yes yes yes
read committed - yes yes
repeatable read - - yes
serializable - - -
  • 有针对性的选择事务的隔离级别，保证数据一致。

8.1 Read Uncommitted

  • dirty read：一个事务会读到另一个事务更新后但未提交的数据。

8.2 Read Committed

  • non repeatable read：一个事务读取了被另一个事务修改前后的两次数据。

8.3 Repeatable Read

  • phantom read：一个不存在的数据事在被另一个事物添加后可以更新。

8.4 Serializable

  • serializable：事务串行执行，效率下降。
  • MySQL使用InnoDB，默认隔离级别Repeatable Read。

9 建表

create database sys50884;
create table temperature (
id int(5) default '0' not null,
date_id date not null,
year_id int(4) not null,
month_id int(2) not null,
day_id int(2) not null,
temp_min double(3,2),
temp_max double(3,2),
temp_av double(3,2),
primary key(id));
insert into temperature values(1,1996-03-01,1996,3,1,-18.9,-6.2,-10.3);
 Toc
 Tags
 Related