mysql的使用
SQL
structure query language:结构化查询语言
在关系型数据库中进行操作标准语言,编程语言
语法规范
- 不区分大小写
- 每句话用;或\g结尾
- 各子句一般分行写
- 关键字不能缩写也不能分行
- 用缩进提高语句的可读性
SQL 分类
DDL
数据定义语言,数据库对象的操作(结构)
create,alter,drop
DML
- 数据操纵语言(数据操作: 增删改查)
insert,update,delete,select
DCL
- 数据的控制语言(授权 : 取消权限)
grant: 授权revoke: 取消授权
TCL
- 事务控制语言
commmit: 提交rollback: 回滚
MySQL使用
MySQL中文乱码
mysql有六处使用了字符集，分别为：client 、connection、database、results、server 、system。
- client是客户端使用的字符集。
- connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。
- database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。
- results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。
- server是服务器安装时指定的默认字符集设定。
- system是数据库系统使用的字符集设定。
查看和修改数据库字符集
1
2
3show variables like 'character%';
set character_set_results=gbk;
set character_set_client=gbk;
MySQL数据类型
在不同的数据库之间,数据类型是有区别的,oracle中数值表示为number
性别:可以使用
bit(1)
| 类型 | 范围 | 备注 |
|---|---|---|
| int | 10位(21亿左右) | |
| float(m,n) | 默认(10,2)精度24位 | 需要声明总长度,小数点位数 |
| double | (16,4),精度53位 | 有可能损失精度 |
| deccimal | 没有默认,必须声明长度和精度 | 用于金融数据保存 |
| char(n) | 定长长度字符串 | 不足空格填补,多出报错 |
| varchar(n) | 可变长度字符串 | |
| text | 大文本 | 最大65535字符 |
| date | 日期 | YYYY-MM-DD |
| time | 时间 | HH:mm:SS |
| datetime | 日期时间 1000-01-01 00:00:00~9999-12-31 23:59:59 |
格式：YYYY-MM-DD HH:MM:SS |
| timestamp | 时间戳 | |
| year | 年 | |
| blob | 存放二进制数据 | 存储图片(一般只存储文件的位置) |
| enum | 枚举 | |
| set | 集合类型 | |
| json | json数据 |
MySQL登陆
1 | mysql -u 用户名 -h 服务器的IP地址 -p密码 |
开启远程访问权限
查看MySQL当前远程访问权限配置,
Host为localhost,即只能本地访问权限1
2use mysql;
select User,authentication_string,Host from user;
改表法
顾名思义,该方法就是直接修改更改”mysql”数据库里的”user”表里的”host”项，从”localhost”改为”%”
1
update user set host='%' where user='root';
授权法
通过GRANT命令可以授予主机远程访问权限
1
2
3
4--赋予任何主机访问权限：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
--允许指定主机(IP地址)访问权限：
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.10.3' IDENTIFIED BY 'root' WITH GRANT OPTION;通过GRANT命令赋权后,需要通过FLUSH PRIVILEGES刷新权限表使修改生效：
1
FLUSH PRIVILEGES;
新版的的mysql版本已经将创建账户和赋予权限的方式分开
1
2
3CREATE USER 'root'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
注意
- 出于安全性考虑，尤其是正式环境下
不推荐直接给root开启远程访问权限。
本案例仅以root用户作为例子做开启远程访问权限的配置,此为演示环境!建议做权限细分和限制
正式环境中，推荐通过创建Mysql用户并给对应的用户赋权的形式来开放远程服务权限，并指定IP地址，赋权时根据用户需求，在GRANT命令中只开放slect、update等权限，做到权限粒度最小化。
MySQL用户管理
新建用户
格式：
create user 用户名@可以登陆的主机ip identified by '密码字符串'；1
2# 创建了一个名为：test 密码为：1234 的用户
create user 'test'@'localhost' identified by '1234';注意：此处的”localhost”，是指该用户只能在本地登录，不能在另外一台机器上远程登录。
也可以指定某台机器可以远程登录。
- ‘192.168.1.%’ 表示192.168.1网段，在创建用户时必须加上
''将网段引起来, '%'表示用户可以从任何地址进行访问,在此处%的作用有点那类似与shell里面的*号通配符
- ‘192.168.1.%’ 表示192.168.1网段，在创建用户时必须加上
查询用户
1 | # 查询用户 |
删除用户
1 | # 删除用户“test” |
更改密码
密码策略
1
2
3
4
5
6
7
8
9
10
11
12
13mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)mysql可以将密码设定的标准和密码长度要求降低。
1
2
3
4
5
6
7
8
9# 5.7
set global validate_password_policy=0;
set global validate_password_length=1;
# 8.0
set global validate_password.policy=0;
set global validate_password.length=1;
set global validate_password.mixed_case_count=0;
set global validate_password.number_count=0;
set global validate_password.special_char_count=0;退出mysql进行服务重启，一般对账户或者配置信息进行修改后都需要进行服务重启。
1
systemctl restart mysqld;
MySQL5.6之前
1
2
3
4
5# 使用set password命令；修改用户“root”的密码为“123456”
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
update mysql.user set password=password('123456') where user='root' and host='localhost';
# 刷新权限
flush privileges;MySQL5.7及后续版本 改密码无password字段password字段改成了
authentication_string1
2
3
4
5
6# 1. 使用mysqladmin命令
mysqladmin -u用户名 -p旧密码 password 新密码
# 2.使用update直接编辑user表，需要刷新；修改用户“root”的密码为“root”
update user set authentication_string = password(“root”) where user = “root”;
# 刷新权限
flush privileges;在mysql5.7.9以后废弃了password字段和password()函数；MySql8.0在user表加了字段authentication_string
authentication_string:字段表示用户密码，而authentication_string字段下只能是mysql加密后的41位字符串密码。所以需要用一下方式来修改root密码：
1
2
3
4
5
6
7
8# 修改密码为123456
ALTER user 'root'@'localhost' IDENTIFIED BY '123456';
# 设置密码使用之前默认身份认证插件
alter user 'root'@'localhost' identified with mysql_native_password BY ‘password’
# 编辑my.cnf文件，在[mysqld]中添加下边的代码,这个需要重启服务才生效。
default_authentication_plugin=mysql_native_password
DCL
权限分类
- 全局层级
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。 - 数据库层级
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。 - 表层级
表权限适用于一个给定的表中的所有列。这些权限存储在mysql.tables_priv表中。 - 列层级
列权限适用于一个给定的表中的单一列。这些权限存储在mysql.columns_priv表中。
权限列表
all所有权限file读或写服务器上的文件select,insert,delete,update表、列的增删改查create,drop数据库/表/索引的操作创建删除alter,index,references修改表结构，创建/删除索引,外键约束的父表
| 权 限 | 作用范围 | 作 用 |
|---|---|---|
| all | 服务器 | 所有权限 |
| select | 表、列 | 选择行 |
| insert | 表、列 | 插入行 |
| update | 表、列 | 更新行 |
| delete | 表 | 删除行 |
| create | 数据库、表、索引 | 创建 |
| drop | 数据库、表、视图 | 删除 |
| alter | 表 | 修改表结构 |
| index | 表 | 创建/删除索引 |
| references | 数据库、表 | 外键约束的父表 |
| reload | 服务器 | 允许使用flush语句 |
| shutdown | 服务器 | 关闭服务 |
| process | 服务器 | 查看线程信息 |
| file | 服务器 | 文件操作 |
| grant option | 数据库、表、存储过程 | 授权 |
| show databases | 服务器 | 查看数据库名称 |
| super | 服务器 | 超级权限 |
| create temporary tables | 表 | 创建临时表 |
| lock tables | 数据库 | 锁表 |
| execute | 存储过程 | 执行 |
| replication client | 服务器 | 允许查看主/从/二进制日志状态 |
| replication slave | 服务器 | 主从复制 |
| create view | 视图 | 创建视图 |
| show view | 视图 | 查看视图 |
| create routine | 存储过程 | 创建存储过程 |
| alter routine | 存储过程 | 修改/删除存储过程 |
| create user | 服务器 | 创建用户 |
| event | 数据库 | 创建/更改/删除/查看事件 |
| trigger | 表 | 触发器 |
| create tablespace | 服务器 | 创建/更改/删除表空间/日志文件 |
| proxy | 服务器 | 代理成为其它用户 |
| usage | 服务器 | 没有权限 |
授权grant
- 语法：
GRANT privileges ON databasename.tablename TO 'username'@'host';grant 权限列表 on 库名.表名 to 用户@主机 identified by '密码字符串';- 用户授权时授权的密码与用户的登录密码相冲突的时候，原密码会失效
- 使用授权密码才能够登录Mysql数据库并拥有相应的授权权限；
授全部权限
all表示所有权限；*.*表示所有的库和所有的表ALL并不包括GRANT OPTION权限（也不包括proxy权限），如果需要使本用户可以给其它用户授权，则可以使用如下语句：
1
2
3GRANT GRANT OPTION ON *.* TO 'user'@'%';
# 或者：
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;示例
1
2
3
4
5
6
7
8
9
10MariaDB [(none)]>
# 创建账户
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
# 赋予权限
grant all on *.* to root@'%'identified by 'root';
# 赋予全部属性包括 GRANT OPTION
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' identified by 'test' WITH GRANT OPTION;
# 刷新权限
mysql> flush privileges;
授部分权限
允许 zhangqi 查询 插入 更新 数据库 test中所有表数据的权限。
1
2MariaDB [(none)]> grant select,insert,update on test.* to zhangqi@'192.168.1.%' identified by 'zhangqi';
MariaDB [(none)]> flush privileges;给数据库开发人员xiaoli 创建表 索引 权限
1
2
3
4
5
6# index 为索引权限
MariaDB [(none)]> grant create,index on *.* to xiaoli@'192.168.1.71' identified by 'xiaoli';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> select * from mysql.user where user='xiaoli'\G;给root_test用户查看mysql.user表的权限
1
2
3
4
5
6
7MariaDB [(none)]> grant select on mysql.user to root_test identified by 'root_test';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select * from mysql.tables_priv where user='root_test'\G;给root_test1用户只可以查看mysql.user user和host列的权限
1
2
3
4MariaDB [(none)]> grant select(user,host) on mysql.user to root_test1 identified by 'root_test1';
MariaDB [(none)]> flush privileges;
查看权限
查看当前用户自己的权限
1
show grants;
查看其他用用户权限
1
2show grants for 用户名@主机名(或是登录ip);
show grants for test@'%';
授权经验原则
权限控制主要出于安全考虑，因此需要遵循一个几个经验原则
只授予能满足需要的最小权限，防止用户干坏事。
比如说用户只是需要查询，那就只给select权限就可以了。
不要给用户赋予update，insert或者delete。
创建用户的时候限制用户的登陆主机
初始化数据库的时候删除没有密码的用户。
为每个用户设置满足密码复杂度的密码
定期清理不需要的用户。回收权限或者删除用户
回收权限
语法：
1
revoke 权限 on 库名.表名 from 用户@主机;
示例
1
2revoke all on *.* from root_test@'%'; #回收全部权限
revoke insert on *.* from root_test@'%'; #回收部分权限
修改权限表改权限
给jim全局权限: mysql.user
1
> grant select on *.* to jim@'%' identified by '123';
给lucy库级权限: mysql.db
1
> grant select on db1.* to lucy@'%' identified by '123';
给lily表级权限: mysql.tables_priv
1
> grant all on db1.t1 to lily@'%' identified by '123';
列级权限: mysql.columns_priv
1
2
3# lilei用户只能查看和更新 db1库，class表中的class列的的内容
#（class）列名
grant select(class) update(class) on db1.class to lilei@'%' identified by '123';
DDL 语言
数据库创建与销毁
1 | # 创建数据库 |
创建表
1 | # 创建表 |
查看表的结构
1 | desc userinfo; |
添加字段:add(追加)
1 | # 追加添加 |
修改字段名称
change:旧字段名称 新名称 类型 约束;modify:字段 类型 约束(不能修改名称)
1 | # 修改字段名称 QQ--> t_qq |
销毁字段drop
1 | # 删除renren字段 |
修改表名称
1 | # rename to 修改表的名称 |
清空表
- (清除数据,不清楚结构)
1 | # 清空表的数据 |
实体完整性
实体: 客观存在的实物,数据库中指的是一条记录.
保证数据记录之间是准确的(能够唯一标识一个实体)
保证实体的完整性:
- 主键约束
- 唯一约束
- 主键自增约束
主键约束
唯一且非空
一张表中只能有一个主键,但是可以有联合主键(多个字段组成)
每一张表必选要设置主键
主键字段的选择
字段必须是唯一的
不要选择业务字段(有真实含义的字段(身份证
- 建议给每张表独立设计一个非业务含义的id字段。
示例：
创建主键:字段后面添加时不能添加联合主键
1
2
3
4
5
6
7
8
9
10CREATE table studetn(
sid int primary key,
sname VARCHAR(20)
);
INSERT INTO student(sid,sname) VALUES(1,'张三');
INSERT INTO student(sid,sname) VALUES(2,'张三');
-- 违反唯一约束： D
-- uplicate entry '1' for key 'PRIMARY'
INSERT INTO student(sid,sname) VALUES(1,'李四');创建表的时候,先给字段,后添加约束
1
2
3
4
5create table student(
sid int,
sname varchar(20),
primary key(sid) # 可以设置联合主键
);表已经创建(alter)
- 表已经创建了,添加约束的时候**,需要表中的数据合理**,不能空和重复
1
2
3# 添加约束(constraint)
# 约束名pk_字段
alter table student add constraint pk_sid PRIMARY key(sid);
唯一约束(unique)
不能重复,可以为空(空只能有一个)
示例
创建表的时候添加unique
1
2
3
4
5
6# 添加唯一约束
create table sun(
uid int PRIMARY KEY,
uname varchar(20),
card VARCHAR(18) unique -- 唯一
);在字段后定义
1
2
3
4
5
6create table sun(
uid int PRIMARY KEY,
uname varchar(20),
card VARCHAR(18),
unique(card)
);表已存在，使用alter修改
1
2
3
4
5
6
7create table sun(
uid int PRIMARY KEY,
uname varchar(20),
card VARCHAR(18)
);
-- ALTER TABLE sun ADD UNIQUE (card)
alter table sun add constraint uq_card UNIQUE(card);
撤销 UNIQUE 约束
1 | -- MySQL: |
主键自增长约束
自增长
- 从0开始递增,第一条为1
ALTER TABLE tablename AUTO_INCREMENT=1000;设置初始值为1000
- oracle:从1开始,每次自身加1
- 从0开始递增,第一条为1
好处:不用关心主键重复问题
示例
- auto_increment:主键自增长
id int(4): 设置长度为4位,使用ZEROFILL不够零填充,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20# 主键自增长
create table student(
sid int primary key auto_increment,
sname VARCHAR(20)
);
# 用0填充
CREATE TABLE student(
-- 自增长，从0开始递增,第一条为1 ZEROFILL 零填充
id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
alter table student MODIFY sid int auto_increment;
-- 自增长字段可以不赋值，自动递增
INSERT INTO student(NAME) VALUES('张三');
-- 结果: id 0001
INSERT INTO student(NAME) VALUES('李四');
INSERT INTO student(NAME) VALUES('王五');删除后自增长的区别
- delete不能影响自增长约束,从上次删除的数开始
DELETE FROM student; - 可以影响自增长约束,从0开始
TRUNCATE TABLE student;
- delete不能影响自增长约束,从上次删除的数开始
删除主键约束
frm:存储数据的元数据信息(结构,约束,索引)
有主键自增长约束的时候,先删除自增长约束,后才能删除主键
1
2
3
4
5
6# 自增长约束
alter table student MODIFY sid int;
# 主键约束
alter table student drop PRIMARY KEY ;
# 唯一约束
drop index uq_sname on student;删除主键pk_PersonID
1
2
3
4
5ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
-- MySQL:
ALTER TABLE Persons DROP PRIMARY KEY
-- SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT pk_PersonID
字段完整性
字段在数据库中也叫域
类型约束:
- 非空约束: 不能为空 not null
- 默认值: default 值
默认值(default)
作用:当用户对使用默认值的字段不插入值的时候，就使用默认值。
注意:
- 对默认值字段插入null是可以的。
示例
1
2
3
4
5
6
7
8
9
10
11
12CREATE TABLE student(
id INT,
NAME VARCHAR(20),
address VARCHAR(20) DEFAULT '广州天河' -- 默认值
)
# 当字段没有插入值的时候，mysql自动给该字段分配默认值
INSERT INTO student(id,NAME) VALUES(1,'张三');
# 注意：默认值的字段允许为null
INSERT INTO student(id,NAME,address) VALUE(2,'李四',NULL);
INSERT INTO student(id,NAME,address) VALUE(3,'王五','广州番禺');
非空(not null)
作用： 限制字段必须赋值
注意：
- 非空字符必须赋值,不能赋null
示例
1
2
3
4
5
6
7
8
9
10
11CREATE TABLE student(
id INT,
NAME VARCHAR(20),
gender VARCHAR(2) NOT NULL -- 非空
)
-- 非空字段必须赋值
INSERT INTO student(id,NAME) VALUES(1,'李四');
-- 非空字符不能插入null
INSERT INTO student(id,NAME,gender) VALUES(1,'李四',NULL); //出错
引用完整性
- 一张表中通用列的取值必须参考另外一张表主键字段
外键约束
主外键关联
- 参考字段通常是参考表的主键
- 外键关联字段名称可以不一样,但是类型必须一致
1
2
3-- constraint 约束名 foreign key (外键) references (主键)
-- add constraint 外键名 foreign key(外键关联字段) references 参考表(参考字段)
alter table student add constraint FK_cid foreign key(cid) references classroom(cid);使用外键约束(foreign key)：约束插入员工表的部门ID字段值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15-- 部门表（主表）
CREATE TABLE dept(
id INT PRIMARY KEY,
deptName VARCHAR(20)
)
-- 修改员工表（副表/从表）
CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptId INT,-- 部门ID的字段参考dept的主键
-- 声明一个外键约束
CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
-- 外键名称 外键 参考表(参考字段)
)注意：
- 被约束的表称为副表，约束别人的表称为主表，外键设置在副表上
- 主表的参考字段通用为主键！
- 添加数据： 先添加主表，再添加副表
- 主表修改数据： 先修改副表，再修改主表
- 主表删除数据： 先删除副表，再删除主表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24INSERT INTO dept(id,deptName) VALUES(1,'软件开发部');
INSERT INTO dept(id,deptName) VALUES(2,'应用维护部');
INSERT INTO dept(id,deptName) VALUES(3,'秘书部');
INSERT INTO employee VALUES(1,'张三',1);
INSERT INTO employee VALUES(2,'李四',1);
INSERT INTO employee VALUES(3,'王五',2);
INSERT INTO employee VALUES(4,'陈六',3);
-- 1）当有了外键约束，添加数据的顺序： 先添加主表，再添加副表数据
员工插入了一个不存在的部门数据
INSERT INTO employee VALUES(5,'陈六',4);
-- 违反外键约束： Cannot add or update a child row: a foreign key constraint fails (`day16`.`employee`, CONSTRAINT `emlyee_dept_fk` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 2）当有了外键约束，修改数据的顺序： 先修改副表，再修改主表数据
//确认副标不存在主表的关联数据 ,才能删除主表数据
UPDATE dept SET id=4 WHERE id=3;//不能修改
UPDATE employee SET deptId=2 WHERE id=4;
-- 3）当有了外键约束，删除数据的顺序： 先删除副表，再删除主表数据
//确认副标不存在主表的关联数据 ,才能删除主表数据
DELETE FROM dept WHERE id=2; //不能删除
DELETE FROM employee WHERE deptId=2; //删除副表
撤销 FOREIGN KEY 约束
1 | -- MySQL: |
自定义完整性
check约束
在mysql中不能使用
CHECK 约束用于限制列中的值的范围。
- 如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
- 如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
创建 CHECK 约束。CHECK 约束规定 “Id_P” 列必须只包含大于 0 的整数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
)
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
-- 为多个列定义 CHECK 约束
-- 命名 CHECK 约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)表已存在的情况下,使用alter修改
1
2
3alter table student add constraint check (sage between 1 and 50);
-- 命名 CHECK 约束，以及为多个列定义 CHECK 约束
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
撤销 CHECK 约束
1 | -- SQL Server / Oracle / MS Access: |
SQL中使用运算符
算数运算符
1 | select 1+1; //2 |
比较运算符
><!=或<>=>=<=true显示:1false显示:0is null//is not nullbetween ... and ...=>[ a , b ]in//not in
逻辑运算符
- and
- or
- !
1 | select 1=1 and 1=2; //0 |
位运算符
- &
- |
- ^(异或)
1 | select 3 & 2; # 2 |
DML
- 数据操作语句:
insert/delete/update/truncate
添加数据insert
insert into tname[(字段名称....)] values(值...)默认插入:给所有的字段添加值,和表中的字段顺序一致
1
insert into classroom VALUES (3,'bd1807')
添加指定的字段,cid是自增长的
1
2--给定字段时,值和给定的字段顺序一致
insert into classroom(cname) values ('bd1808');添加多条数据:使用逗号隔开
1
insert into classroom(cname) value ('bd1809'),('bd1810'),('bd1811');
将classroom 的值 整体复制到 classroom1
1
2
3# 将classroom 的值 整体复制到 classroom1
insert into classroom1 select * from
create table classroom1 select * from classroom;复制表的结构
1
create table classroom1 select * from classroom where 0;
删除数据delete
delete是逐行删除,不影响自增长.自是修改数据(自增不会重置)
- 只能删除表的数据，不能删除表的约束
- 使用delete from删除的数据可以回滚（事务）
truncate 清空,文件级别的清空,(自增重置)
删除所有
1
delete from classroom;
删除指定的记录
1
delete from classroom where cid=2;
truncate table: 可以全表删除
- 不能带条件删除
- 即可以删除表的数据，也可以删除表的约束
- 使用truncate table删除的数据不能回滚
TRUNCATE TABLE student;
修改数据update
使用的时候不带条件是对整张表修改,推荐使用带条件(where)的修改
update 表名 set 字段 = 值 where 字段 = 值1
2
3update classroom set cname='1806' where cid =1;
-- 修改多个字段,注意: SET 字段名=值,字段名=值,....
UPDATE student SET gender='男',age=30 WHERE id=2;
DQL
普通查询select
格式：
select 字段|表达式 from 表|视图|结果集 (where条件)示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28# 查询
select * from emp;
# 查询所有员工的姓名和工资
select ename,sal from emp;
# 查询时添加常量列
SELECT id,NAME,gender,age,'常量列' AS '年级' FROM student;
# 查询时合并列 查询每个学生的servlet和jsp的总成绩
SELECT id,NAME,(servlet+jsp) AS '总成绩' FROM student;
-- 注意：合并列只能合并数值类型的字段,下面语句报错
SELECT id,(NAME+servlet) FROM student;
# 查询工资大于2000
select * from emp where sal>=2000;
# 查询工资1000~2000
select * from emp where sal between 1000 and 2000;
# 查询id为2，或姓名为张三的学生
SELECT * FROM student WHERE id=2 OR NAME='张三';
# 查询地址为空的学生（包括null和空字符串）
SELECT * FROM student WHERE address IS NULL OR address='';
# 查询有地址的学生(不包括null和空字符串)
SELECT * FROM student WHERE address IS NOT NULL AND address<>'';
集合查询(in ([值 , 值 ,…]))
1 | select * from emp where empno=7521 or `empno`=7369 or empno = 7788; |
取别名 (as 别名)
(as) 别名简化作解释说明
可以使用空格替换as
1
2
3
4
5
6
7select 1+1 count; # 将结果用count表示
select ename,sal*1.5 new from emp; # 将结果用new表示
select emp.ename from emp;
select e.sal from emp e;
去除重复distinct
在查询前面添加 distinct
1
2select DISTINCT job from emp;
select DISTINCT(job) from emp;
模糊查询:like
%: 表示任意个字符_:(下划线)表示一个字符
1 | # 模糊查询 |
聚合查询
常用聚合函数
sum(): 求和函数avg(): 平均分max(): 最大值min(): 最小值count(列): 统计不包含null的记录数count(1)和count(*)之间没有区别,不会去过滤空值
需求：查询学生的servlet的总成绩 (sum() )
SELECT SUM(servlet) AS 'servlet的总成绩' FROM student;需求： 查询学生的servlet的平均分
SELECT AVG(servlet) AS 'servlet的平均分' FROM student;需求: 查询当前servlet最高分
SELECT MAX(servlet) AS '最高分' FROM student;需求： 查询最低分
SELECT MIN(servlet) AS '最低分' FROM student;需求： 统计当前有多少学生(count(字段))
SELECT COUNT(*) FROM student;– 包含null值SELECT COUNT(id) FROM student;
注意:
- count(列) 函数统计的数量不包含null
- MySQL会对count（*）做优化。
- 如果列为主键，count(列名)效率优于count(1)
- 如果列不为主键，count(1)效率优于count(列名)
- 如果表中存在主键，count(主键列名)效率最优
- 如果表中只有一列，则
count(*)效率最优 - 如果表有多列，且不存在主键，则
count(1)效率优于count(*)
Limit
LIMIT 接受一个或两个数字参数。
- 参数必须是整数常量。
- 如果给定两个参数
- 第一个参数指定第一个返回记录行的偏移量
- 第二个参数指定返回记录行的最大数目。
- 初始记录行的偏移量是 0(而不是 1)：
- 为了与 PostgreSQL 兼容，MySQL 也支持句法：
LIMIT # OFFSET #。
格式：
limit （起始行,查询几行）1
2
3
4
5# 起始行从0开始,取5条记录
select * from emp limit 0,5;
# 取部门为10的员工中的最大值
select * from emp where deptno=10 order BY emp.sal desc limit 0,1;分页：当前页 每页显示多少条
分页查询当前页的数据的sql:
SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显示多少条;
需求： 查询第1,2条记录（第1页的数据）
SELECT * FROM student LIMIT 0,2;查询第3,4条记录（第2页的数据）
SELECT * FROM student LIMIT 2,2;查询第5,6条记录（第3页的数据）
SELECT * FROM student LIMIT 4,2;查询第7,8条记录 (没有记录不显示)
SELECT * FROM student LIMIT 6,2;
结果排序(order by)
语法 ：
order by 字段 asc/descasc: 顺序，正序。数值：递增，字母：自然顺序（a-z）
desc: 倒序，反序。数值：递减，字母：自然反序(z-a)
默认情况下，按照插入记录顺序排序
需求： 按照id顺序排序
SELECT * FROM student ORDER BY id ASC;SELECT * FROM student ORDER BY id;– 默认正序SELECT * FROM student ORDER BY id DESC;– 反序注意：多个排序条件使用逗号隔开
需求： 按照servlet正序，按照jsp的倒序
1
2
3SELECT * FROM student ORDER BY servlet ASC,jsp DESC;
select * from emp where deptno=10 order BY emp.sal desc;
select * from emp where deptno=10 order BY emp.sal desc,emp.ename desc;
分组查询(group by)
需求：查询男女的人数,分组依据要显示出来
1
2
3
4
5
6
7-- 把学生按照性别分组(GROUP BY gender)
-- 统计每组的人数(COUNT(*))
select ssex count(*) from group by sex;
结果：
男 3
女 2
分组查询后筛选(having)
- 注意： 分组之前条件可以使用where关键字，分组之前条件使用having关键字
- 需求： 查询总人数大于2的性别
1 | -- 1. 查询男女的人数 |
函数
单行函数
- 返回值只有一条记录
数学函数
| 函数名 | 备注 |
|---|---|
| PI() | π |
| ceil() | 向上取整ceil(10.1) = 11 |
| floor() | 向下取整floor (10.1) = 10 |
| round() | 四舍五入 |
| Mod(M,N) | M MOD N 求余(M%N) 10%3=1 |
| rand() | 随机数 |
| Pow(M,N) | 幂运算M^N求指数 |
| Abs(X) | 绝对值abs(-10.9) = 10 |
| Format(X，D) | 格式化千分位数值format(1234567.456, 2) =1,234,567.46 |
| Sqrt(X) | 算术平方根 |
| TRUNCATE(X,D) | 截取D位小数 |
示例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21select PI(); # 3.141593
select ceil(12.3);# 13
select ceil(-12.3);;#12
select floor(12.3); #12
select floor(-12.3); #-13
select round(3.5); #4
select round(3.45); #3
select round(3.45,1); #3.5
select round(3.45,-1); #0
select mod(5,2); # 1
select rand();[0,1)
# 随机从emp获取二条记录
select * from emp ORDER BY rand() LIMIT 2;
select pow(2,3); # 2^3=8
字符函数
| 函数名 | 备注 |
|---|---|
| length(string) | 获取字符的长度，字节 |
| CHAR_LENGTH(string) | string的字符个数 |
| lower(‘THIS’) /LCASE(string ) | 字符转为小写字母 |
| upper(‘this’) | 字符转为大写字母 |
| SUBSTRING(str ,position [,length ]) | 从str的position开始,取length个字符，下标从1开始 |
| REPLACE(str ,search_str ,replace_str) | 在str中用replace_str替换search_str |
| INSTR(string ,substring ) | 返回substring首次在string中出现的位置 |
| CONCAT(string [,… ]) | 连接字串 |
| CHARSET(str) | 返回字串字符集 |
| LEFT(string ,length ) | 从string中的左边起取length个字符 |
| LOAD_FILE(file_name) | 从文件读取内容 |
| LOCATE(substring , string [,start_position ]) | 同INSTR,但可指定开始位置 |
| LPAD(string ,length ,pad ) | 重复用pad加在string开头,直到字串长度为length |
| LTRIM(string ) | 去除前端空格 |
| REPEAT(string ,count ) | 重复count次 |
| RPAD(string ,length ,pad) | 在str后用pad补充,直到长度为length |
| RTRIM(string ) | 去除后端空格 |
| trim(‘str’) | 去除字符二端的空格 |
| STRCMP(string1 ,string2 ) | 逐字符比较两字串大小 |
示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20select length('this is zs') # 10
select upper('this') # THIS
select lower('THIS') # this
select substr('this is zs',1,6) #this i
select replace('this is zs','this','he'); #he is zs
select trim(' this is '); #去两端空格 this is
select LPAD('aa',10,'*'); #********aa
select LPAD('ab',1,'*'); #a
select rpad('aa',10,'*'); #aa********
select rpad('ab',1,'*'); #a
日期函数
| 函数名 | 备注 |
|---|---|
| now(),current_timestamp() | 当前时间 |
| sysdate | 获取系统时间 |
| curdate() /current_date() | 获取日期 : 年月日 |
| curtime() / current_time() | 获取时间 : 时分秒 |
| year(date) | 获取指定时间的年份 |
| month(date) | 获取指定时间的月份 |
| day(date) | 获取指定时间的天数 |
| last_day(date) | 获取指点时间的月份的最后一天 |
| date_add(时间 , interval 值 ,类型) | 值可以为正负,类型可以是year/month/day |
| Date(‘yyyy-mm-dd HH;ii:ss’) | 获取日期部分 |
| Time(‘yyyy-mm-dd HH;ii:ss’) | 获取时间部分 |
| Date_format(‘yyyy-mm-dd HH;ii:ss’,’%D %y %a %d %m %b %j’); | |
| Unix_timestamp() | 获得unix时间戳 |
| From_unixtime(); | 从时间戳获得时间 |
示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25select NOW(); #当前时间 2018-08-29 21:17:54
select SYSDATE(); #获取系统时间 2018-08-29 21:18:04
select CURRENT_DATE(); 2018-08-29
select CURDATE(); 2018-08-29
select CURRENT_TIME(); 21:18:47
select CURTIME(); 21:18:47
select YEAR('1998-09-09'); 1998
select YEAR(NOW()); 2018
select MONTH(NOW()); 8
select DAY(NOW()); 29
#获取当前月最后一天
select LAST_DAY('2018-02-01'); 2018-02-28
#日期计算
select DATE_ADD(NOW(),interval 2 MONTH); 2018-10-29 21:20:28
聚合函数
- 对数据整体进行运算,查询语句中不能出现单个字段
- min():最小值
- max():最大值
- avg():平均值
- count():总计
- sum():求和
1 | select max(sal) from emp; |
分组函数
使用分组的依据可以出现在select 查询的语句中
where在group by 之后
where中不能使用聚合函数
having
having 代替where ,分组之后进行检索
可以使用聚合函数
1 | # 分组 group by 分组条件 having:分组之后进行检索 |
加密函数
- MD5(str):32为位
- SHA(str):40位
- password(str):41位
1 | select MD5('root'); #63a9f0ea7bb98050796b649e85481845 |
级联操作
当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表！
但是，我们希望直接修改或删除主表数据，从而影响副表数据。可以使用级联操作实现！！！
级联修改：
ON UPDATE CASCADE级联删除：
ON DELETE CASCADE联操作必须在外键基础上使用
示例
1
2
3
4
5
6
7
8
9
10
11CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptId INT,-- 把部门名称改为部门ID
-- 声明一个外键约束
CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
-- 外键名称 外键 参考表(参考字段)
ON UPDATE CASCADE -- 级联更新
ON DELETE CASCADE -- 级联修改
)级联操作
1
2
3
4
5
6
7-- 级联修改（修改）
-- 直接修改部门
UPDATE dept SET id=5 WHERE id=4; //属于这个部门的员工的部门id也修改了
-- 级联删除
-- 直接删除部门
DELETE FROM dept WHERE id=1; //属于这个部门的员工也删除了
关联查询（连接查询）
- 查询员工编号为 7788 的员工姓名 和所在的部门的名称
内连接
特点
- 关联表中都出现的字段值最终才能出现在结果集中
- 内连接与连接顺序无关
- 内连接不分主从表
格式：
1
2
3select where
inner join ...on...
inner join .... using(通用列（连接字段）)：- 通用列的名称要一样
- 不会出现二次通用列（去除重复的字段）
1 | # 查询员工编号为 7788 的员工姓名 和所在的部门的名称 |
外连接
特点：有主从表之分
- 依次遍历主表中的记录，与从表记录进行匹配
- 如果匹配，连接显示
- 如果不匹配，以null 填充
左外连接
left[outer] join ...on- 前面是主表，后面是从表
右外连接：
right[outer] join ... on- 前面是从表，后面是主表
1 | # 左外连接: 14 |
等值连接
自然连接(natural join),都是等值连接
- 不需要声明某个字段之间相等,是一种特殊的等值连接
- 自然连接是除去重复属性的等值连接
- 连接属性X与Y具有相同属性组时，把在连接结果中重复的属性列去掉。
等值连接特点
- 并不去掉重复的属性列。
- 等值连接不一定是自然连接
1 | # 自然连接 |
自连接
- 可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。
1 | # 查询所有员工和他的上级领导 |
子查询（嵌套查询）
- 将一个查询结果当作另外一个查询的条件或表达结果集
- 是最接近思考方式，最自然的查询
单行子查询
- 子查询的返回结果只有一条记录，
- 常用操作符
>,<,=,<>等
1 | # 查询大于scott 薪水的员工信息 |
多行子查询
子查询的返回结果有多条记录
常用操作符
in,any,all搭配使用
1
2
3
4
5= any ： 相当于 in
> any : 大于最小值
< any ： 小于最大值
> all : 大于最大值
< all : 小于最小值示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34# 查询员工的工资在20号部门的工资内且不在20号部门
SELECT * FROM emp
WHERE sal IN (
SELECT DISTINCT sal FROM emp WHERE deptno = 20 )
AND deptno <> 20
# 查询20号部门除了工资最高员工 的 员工信息
select * from emp where sal<any(
select distinct sal from emp where deptno=20)
and deptno=20
# 查询大于20号部门的平均工资的20号部门的员工信息
select * from emp where sal>
(select avg(sal) from emp where deptno =20)
and deptno =20
# 查询大于所在部门的平均工资的员工信息
1. 主查询遍历整个emp表
2. 主查询读取某一条记录的deptno值，将该值交给子查询
3. 子查询根据主查询的传来的deptno值，查询出指定部门的平均工资然后将整体结果返回给主查询
4. 主查询根据子查询的结果最终执行。
SELECT * FROM emp
WHERE sal > (
SELECT avg( sal ) FROM emp e2
GROUP BY deptno
HAVING emp.deptno = e2.deptno
) ;
# 查询工资> 20号部门的所有员工的信息
select * from emp where sal>(
select max(sal) from emp where deptno=20);
select * from emp where sal>all(
select sal from emp where deptno=20);
exists
exists 和 in的区别
in 先执行子查询，将结果返回给主查询，主查询继续执行
exists :先执行主查询，将主查询的值依次在子查询中进行匹配，根据是否匹配返回True或者false,如果是true就连接展示否则不展示，
exists :实际上是主查询传值给子查询
1 | # 查询薪水> 2000 的员工所在的部门信息 |
多表联查性能优化
子查询和连接查询区别
- 子查询查询条件和结果放在一张表
- 执行效率并不高，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程
- 连接查询（join）
- 可以使用连接查询（JOIN）代替子查询，连接查询不需要建立临时表，因此其速度比子查询快
优化的本质
就是(join on 和where的执行顺序）
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户
- 注意：这张临时表是数据库（MySQL自动生成）
在使用left jion on时，on和where条件的区别如下：
- on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录
- where条件是在临时表生成好后，再对临时表进行过滤的条件（这时已经和left join没关系了），条件不为真的就全部过滤掉
left join on + where条件查询的索引优化实例分析：
sql：select * from A left join B on A.c = B.c where A.employee_id = 3- 解读： A表left join B表，并且指定A表中的employee_id为一个具体的值
假设A表，B表均有10000多条数据；
- 使用上面的sql查询时间达到16秒（在c字段不是任何索引，用explain分析得知，AB表都使用了全表查询，效率极低）
开始优化上面sql
- 给AB表列c都加索引（仅用了0.1s，但是分析后显示表A依然进行了全表扫描）
- 思考：为什么全表扫描的不是表B
- 因为Mysql内部的优化，使用小表驱动大表，它在估算到必须有一个表要全表扫描的话，一定会选择那个数据量更小的表去全表扫描，
- 也就是说，在这个查询中，因为on以后的where条件列并没有使用到索引，所以mysql的优化只用到了表B的c索引，没有用到表A的索引！
我们有where条件查询，不需要全表扫描，此时就需要where条件生效，操作及分析如下：
- 将A表中的索引改为employee_id+c（经验证两个所以都使用了，方案可行）
- 思考：sql执行 from中的on应该是优先于where语句的，为什么这里employee_id反而在c之前，有违常理
- 因为Mysql内部优化，这一句Sql在执行的时候首先是选择了使用表B的索引来进行优化，将表A单独放出来进行后续的操作，
然后，又发现了where语句中A.employee_id有一个聚合索引，并且employee_id处于索引头，所以这个聚合索引是可用的，所以自然使用了此索引 - 即使把聚合索引后面的列c删掉，与使用聚合索引的效果是一样的，之前全表查询，现在根据条件只查询了满足条件的，时间大幅缩短
索引的重要性
- 连接查询通过两张表中符合连接关系的字段来建立两张表的关联，通常包括内连接、左外连接、右外连接和全连接
- 内连接会保留两张表中共有的那部分记录，因此最后产生的连接表记录数最少；
- 全连接会保留两张表中所有的记录，因此最后产生的连接表记录数最多；
- 左外连接会保留左表的全部记录，右外连接会保留右表的全部记录，因此最后产生的连接表记录数处于内连接和外连接之间。
- 我们可以给关联表的字段添加索引来减少查询次数，提高查询效率
- 使用多表关联时，一般遵循以下规则：
- 左连接：一般给右边表的关联字段建立索引；
- 右关联：一般给左边表的关联字段建立索引；
- 内连接：一般给关联表的任意一边的关联字段建立索引即可
- 使用多表关联时，一般遵循以下规则：
举例：学生表student（id，name），课程表class(id,student_id,class),各有10000条数据
1
2SELECT a.id, name FROM student a
LEFT JOIN class b ON a.id = b.student_id- 首先用explain查看这个语句的查询执行计划，可以看到type都为ALL，即在student表和class表中都使用的全表扫描
改进：试着给class表的student_id字段添加索引
1
alter table class add index class_index(student_id)
- 然后再次执行查询，发现速度非常快,这就是效率改进的关键点所在
- 当连接查询时产生的连接表过大时，为了防止查询次数过多，我们要经常使用索引来减少查询次数，提高查询效率
联合查询union
联合的结果集必须一致
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
使用or会导致索引失败
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
- union all
1 | # 查询20号部门 以及工资>2000的员工信息 |
视图
数据库对象,它是一个虚拟表,底层并非以键值形式保存数据
在执行过程中**动态从基表中获取数据(**视图以表为底层来查询)
增删改视图的时候,表的数据也会修改
视图一般只做查询不做增删改的操作。
视图的目的通常是从优点考虑的
- 一个是安全（敏感字段）
- 第二是高效
- 第三是可以定制化数据（如多表查询）。
视图实际上表的上层结构,表的映射.
视图的创建
1 | # 创建视图 |
视图保护
with check option:透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）- 保护视图数据,只能在视图范围之内进行数据的修改
- 修改视图会修改底层基表的数据
- 视图一般只执行查询,不执行增删改
- 对于update,有with check option，要保证update后，数据要被视图查询出来
- 对于delete,有无with check option都一样
- 对于insert,有with check option，要保证insert后，数据要被视图查询出来
- 对于没有where 子句的视图，使用with check option是多余的
1 | # 获取张姓的人员 |
销毁
- 命令：
drop view view_name - 视图在物理上是不存在的，它实际上是不存在的。只是一个查询结果，是一个被存储的查询。
- 与create table语句不同，create table语句在系统目录中保存表，而create view 语句只保存视图的定义。
- 所以Drop view语句删除视图时，删除的也是视图的定义，对实际表中的数据并没有影响。
视图的优点
- 安全性:保密敏感数据,(视图中不给出敏感信息)
- 高效性:提高查询效率
- 定制化数据: 可以将多张表中经常展示数据放置在视图中快速查询(可以多张表联合查询放入视图)
变量声明
全局变量
- 全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）
- 查看所有全局变量：
show variables - 查看某个全局变量：
select @@变量名character_set_client: mysql服务器的接收数据的编码character_set_results：mysql服务器输出数据的编码
- 修改全局变量：
set 变量名=新值
会话变量
- 会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
- 定义会话变量:
set @变量=值 - 查看会话变量:
select @变量
局部变量
局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！
声明:
DECLARE var_name[,...] type [DEFAULT value]- 要给变量提供一个默认值，请包含一个DEFAULT子句。
- 值不需要为一个常数.可以被指定为一个表达式。
- 如果没有DEFAULT子句，初始值为NULL。
赋值
- 使用语序使用 set 和 select into语句为变量赋值。
示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29-- 学生表
create table join_student(
stu_id int not null auto_increment,
stu_no char(10),
class_id int not null,
stu_name varchar(10),
stu_info text,
primary key (stu_id)
);
-- 获取当前班级内，最大的学号。
drop function if exists sno;
delimiter $$ #在包含有语句块时 可以更换语句结束符“；” 为“$$”
create function sno(c_id int)returns char(10)
begin
declare last_no char(10); #声明一个局部变量 用来保存当前最大的学号， 如果没有就为null
declare class_name char(10);
select stu_no from join_student where class_id=c_id order by stu_no desc limit 1 into last_no;
if last_no is null then #如果为空代表当前班级没有学生 从1开始，获得班级名字
return concat ((select c_name from join_class where id=c_id into class_name),'001'); #concat() 函数的作用是连接字符串。
else
return concat(left(last_no,7),lpad(right(last_no,3) + 1, 3, '0'));
end if;
#return @last_no;
end
$$
delimiter ;
控制语句
if语句
格式：
If -- then -- elseif -- then --else --end if;(end if 后面有分号;)示例1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17# 控制语句
# 成绩分级
delimiter //;
create PROCEDURE score_lever1( score int)
BEGIN
declare v_level varchar(20);# 变量的声明
if score > 80 then
set v_level = 'A'; # 变量的赋值
elseif score>=60 then
set v_level = 'B';
else
set v_level = 'c';
end if;
select v_level;
end;
call score_lever1(90); # A示例2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23-- 需求：输入一个整数，
如果1，则返回“星期一”,
如果2，返回“星期二”,
如果3，返回“星期三”。
其他数字，返回“错误输入”;
DELIMITER $
CREATE PROCEDURE pro_testIf(IN n INT,OUT str VARCHAR(20))
BEGIN
IF n=1 THEN
SET str = '星期一';
ELSEIF n=2 THEN
SET str='星期二';
ELSEIF n=3 THEN
SET str = '星期四';
ELSE
SET str='输入错误';
END IF;
END $
//使用
CALL pro_testIf(1,@str)
SELECT @str -- str 星期一
循环语句
循环需要声明会话变量，保存中间值
格式
1
2
3
4
5
6
7
8
9
10
11
12
13-- 格式1
while 条件
do
-- 循环体;
end while;
-- 格式2
loop....
end loop(死循环); 判断: leave
repeat until ... end repeat;(死循环)示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14create procedure sum1(a int)
begin
declare sum int default 0; -- default 是指定该变量的默认值
declare i int default 1;
while i<=a DO -- 循环开始
set sum=sum+i;
set i=i+1;
end while; -- 循环结束
select sum; -- 输出结果
end
-- 执行存储过程
call sum1(100);
-- 删除存储过程
drop procedure if exists sum1
loop循环
- leave 类似于break 语句，跳出循环，跳出 begin end
- iterate 类似于continue ，结束本次循环
1
2
3
4
5loop_name:loop
if 条件 THEN -- 满足条件时离开循环
leave loop_name; -- 和 break 差不多都是结束训话
end if；
end loop;loop示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34create procedure sum2(a int)
begin
declare sum int default 0;
declare i int default 1;
loop_name:loop -- 循环开始
if i>a then
leave loop_name; -- 判断条件成立则结束循环 好比java中的 boeak
end if;
set sum=sum+i;
set i=i+1;
end loop; -- 循环结束
select sum; -- 输出结果
end
-- 执行存储过程
call sum2(100);
-- 删除存储过程
drop procedure if exists sum2
-- 实例2
DECLARE num INT;
DECLARE str VARCHAR(255);
SET num =1;
SET my_string = '';
loop_label: LOOP
IF num <10 THEN
LEAVE loop_label;-- 相当于java中的break
ENDIF;
SET num = num +1;
IF(num mod 3)THEN
ITERATE loop_label; -- iterate相当于java中continue
ELSE
SET my_string =CONCAT(my_string,num,',');
ENDIF;
END LOOP;repeat 循环语法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20repeat
循环体
until 条件 end repeat;
-- 实例；
create procedure sum3(a int)
begin
declare sum int default 0;
declare i int default 1;
repeat -- 循环开始
set sum=sum+i;
set i=i+1;
until i>a end repeat; -- 循环结束
select sum; -- 输出结果
end
-- 执行存储过程
call sum3(100);
-- 删除存储过程
drop procedure if exists sum3
CASE语句
格式
1
2
3
4
5
6CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]
...
[ELSE statement_list]
END CASE;
存储过程
- 服务器端运行的,可重复调用的sql代码块
- 存储过程是带有逻辑的sql语句存储过程带上流程控制语句(if,while)
特点
一组存储和执行的在服务器端的程序,运行于服务器端
优点:
- 简化开发
- 执行效率高(不需要校验和验证)
缺点:
- 程序保持在服务器端,占用服务器资源
- 数据迁移麻烦
- 调试,编写程序不方便
无参数
示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16delimiter $ --声明存储过程的结束符
create procedure pro_test() --存储过程名称(参数列表)
begin -- 开始
/////////////////////////////
//// SQL 语句 + 流程控制语句//
/////////////////////////////
end $
delimiter // # 设置//为结束标志,分号就不是结束标志了
create procedure sel_emp()
BEGIN
select ename,dname from emp,dept where emp.deptno=dept.deptno;
end //
# 使用
call sel_emp();
传入模式(默认为 in)
(in 参数名 类型):表示输入参数,可以携带数据到存储过程中示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19# 根据编号查询员工
delimiter //
create PROCEDURE findEmpByNo(eno int)
BEGIN
select * from emp where deptno = eno;
end;
call findEmpByNo(10);
-- 需求: 传入一个员工的id,查询员工信息
delimiter $
create procedure pro_findById(in eid int) -- in : 输入参数
begin
select * from employee where id = eid;
End $
// 调用 ////////
call pro_findById(4);
输出模式
out: 定义一个输出变量
into:将结果赋值给变量
示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34# 根据员工编号查询员工姓名
delimiter //;
create PROCEDURE findNameByNO(eno int ,out v_name varchar(20))
BEGIN
SELECT ename into v_name from emp where empno = eno;
END
# 使用
call findNameByNO(7788,@name);
select @name;
-- set方式赋值
delimiter $
create procedure pro_testOut(Out str varchar(20)) --out: 输出参数
begin
-- 给参数赋值
set str = "hello java";
End $
使用:
call pro_testOut(@name);
selec @name
-- into赋值
DELIMITER $
CREATE PROCEDURE pro_finById2(IN eid INT,OUT vname VARCHAR(20))
BEGIN
SELECT empName INTO vname FROM employee WHERE id =eid;
END $
//使用
CALL pro_finById2(1,@name)
SELECT @name
输入输出模式
1 | # 根据员工姓名查询员工职位 |
删除/执行存储过程
- 执行存储过程
call sum1(100); - 删除存储过程
drop procedure if exists sum1DROP PROCEDURE pro_testOut(存储过程名);
存储函数
存储在服务器端,有返回值,函数作为sql一部分使用
在函数中，不能使用select语句来打印数据，使用select来查询数据并且赋值给变量，这个是可以的。
函数是要有返回值的。
删除:
Drop function if exists function_name;查看
Show function status like 'partten';Show create function function_name;
修改：
Alter function function_name函数选项;创建格式
1
2
3
4
5
6create function functionName([parm type],[parm1 type],……)
returns 返回值类型
begin
语句；
return 值；
end;示例
1
2
3
4
5
6
7
8
9
10create FUNCTION functionTest(p_con VARCHAR(400))
RETURNS VARCHAR(400)
BEGIN
DECLARE v_con VARCHAR(400);
set v_con = p_con;
SELECT p_con into v_con;
return v_con;
end;
select functionTest('baiduc');
触发器
- 不要添加过多触发器 -> 降效率
- 存储程序中不能使用事务控制
查看触发器
SHOW TRIGGERS ;SELECT * FROM information_schema. triggers ;SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='触发器名';information_schema.triggers：表示information_schema数据库下面的triggers表。
删除触发器
DROP TRIGGER 触发器名 ;格式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20CREATE OR REPLACE TRIGGER trigger_name<before | after | instead of> <insert | update | delete> ON table_name
[FOR EACH ROW]
WHEN (condition)
DECLARE
BEGIN
--触发器代码
END;
- Trigger_name是触发器的名称。
- <before | after | instead of>可以选择before或者after或instead of。
- Before表示在DML语句实施前执行触发器，
- 而after表示在在dml语句实施之后执行触发器，
- instead of触发器用在对视图的更新上。
- <insert | update | delete>可以选择一个或多个DML语句，如果选择多个，则用or分开，如：insert or update。
- Table_name是触发器关联的表名。
- [FOR EACH ROW]为可选项，
- 如果注明了FOR EACH ROW，则说明了该触发器是一个行级的触发器，DML语句处理每条记录都会执行触发器；
- 否则是一个语句级的触发器，每个DML语句触发一次。
- WHEN后跟的condition是触发器的响应条件，只对行级触发器有效，当操作的记录满足condition时，触发器才被执行，否则不执行。
- Condition中可以通过new对象和old对象（注意区别于前面的:new和:old，在代码中引用需要加上冒号）来引用操作的记录。需求： 当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17-- 创建日志表
CREATE TABLE test_log(
id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20)
)
-- 创建触发器(添加)
CREATE TRIGGER tri_update AFTER UPDATE or delete
ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES ('员工表修改了一条记录');
-- 测试
UPDATE employee SET empName ='123' WHERE id =5
-- 查看测试结果
SELECT * FROM test_log;
--0001 员工表修改了一条记录调用存储过程
- 在CLASS_PROPERTY_REL删除记录时，调用存储过程gen_ci_property_value
1
2
3
4
5
6
7
8DELIMITER $$
CREATE TRIGGER cpr_del AFTER DELETE ON CLASS_PROPERTY_REL FOR EACH ROW
BEGIN
CALL gen_ci_property_value();
END$$
DELIMITER ; --是把定界符再改回默认
mysql触发器new old
“NEW . column_name”或者”OLD . column_name”.这样在技术上处理（NEW | OLD . column_name）新和旧的列名属于创建了过渡变量（”transition variables”）。
对于INSERT语句,只有NEW是合法的；
对于DELETE语句，只有OLD才合法；而
UPDATE语句可以在和NEW以及OLD同时使用。
例子
1
2
3
4
5
6
7
8
9
10
11
12CREATE TRIGGER tr1
BEFORE UPDATE ON t22
FOR EACH ROW
BEGIN
SET @old = OLD.s1;
SET @new = NEW.s1;
END;
-- 现在如果t22表中的s1列的值是55，
-- 那么执行了"UPDATE t21 SET s1 = s1 + 1"之后
@old的值会变成55，
@new的值将会变成56。
索引
- 提升查询效率而创建数据结构
- 不填加索引,全表扫描
- INNODB本身存储的就是以树的形式.
- 树的深度越浅,检索越好,尽量平衡
索引的分类
B-Tress索引:
- mysql使用B+Tree:方法来卫华索引列数据
B+树是一个多路搜索树
不同的存储引擎对索引的存储策略不一致
myisam:叶子节点中保存记录的地址
innodb:叶子节点中直接保存相应数据
位图索引
哈希索引
创建
普通索引
1
2
3
4
5
6
7
8
9
10#根据ename 查询记录
# 不适用索引
explain select * from emp where ename = 'scott'
type: ALL //最慢all(全表扫描),最好能达到 reg ,最好 system
rows: 14
# 普通索引
create index index_ename on emp(ename);
type: ref
rows: 1唯一索引
1
create unique index index_name on 表(字段);
全文索引
空间索引
复合索引: 多列作为索引字段 on 表(字段,字段)
删除
- 格式
drop index 名字 on 表
索引的选择
索引需要单独的文件来保存维护
表数据发生变化需要维护索引表
适合添加索引的条件
- 表的数据量足够大(10万条左右)
- 增删改操作较少(3%~%5)
- 字段为高基数列(不一样的数据较多的:高基数)
索引的说明
索引不适合添加过多
经常作为查询条件的列适合作为索引
有些特殊情况下,索引会失效
如: 给enamet添加索引后, ename like ‘%s%’,包含的情况,走的是全表
事务
存储引擎
数据库底层软件组织,DBMS通过存储引擎实现对数据库的操作;MySQL的核心就是存储引擎.
MySQL中可以设置多种存储引擎,不同的存储引擎在索引,存储,以及锁策略上是不同的
MySQL5.5之前,采用myisam存储引擎,支持全文搜索,不支持事务
mysql5.5之后,默认采用innidb存储引擎,支持事务以及行锁定
什么是事务
- 事务保证数据一致性,一组DML操作要么同时成功,要么同时失败
事务的特性
转账的示例
1
2
3
4
5# 张三 转账---> 李四 ---->100
update account set money =money -100 where name='zs';
delete form aa;
## 出现错误,后面执行失败,李四没有收到钱
update account set money =money +100 where name='ls';事务的acid特性
- 原子性:放在同一个事务中的一组操作是不可分割的.
- 一致性:在事务的执行前后,总体的状态保持不变
- 隔离性:并发事务之间互相不能干扰
- 持久性:事务执行之后,将永久化到数据库
事务的语法
一组DML操作,放到同一个事务中进行处理
mysql 数据库采用自动事务提交:
- 每次执行一个DML操作,系统会自动创建一个事务,并且执行后,自动提交
查看MySQL的事务:自动提交
- show variables like ‘autocommit’;
关闭MySQL的自动提交
- set autocommit = 0;
- set autocommit = 1;
显式开启事务(可以不写)
- 提交 commit;
- 回滚 roback;
1
2
3
4
5
6-- 1. 开启事务
start transaction;
begin
-- 2.一组DML操作:
-- 3. 提交/回滚事务
end;示例
1
2
3
4
5
6
7
8
9# 显示开启事务
Start transaction;
update account set money =money -100 where name='zs';
update account set money =money +100 where name='ls';
# 手动提交
commit;
# 手动回滚
rollback;
并发事务产生的问题
- 脏读:在一个事务的执行范围内读到了另一事务未提交的数据
- 不可重复读:一个事务在只读范围内,被另一个事务修改并提交事务,导致多次读取事务不一致的问题.
- 幻读(虚读):一个事务只读范围内,被另一个事务删除 或者添加数据,导致数据读取不一致.
事务的隔离级别
- 读未提交:不能处理任何问题
- 读已经提交(可以修改): 只能读到提交后的数据问题,解决脏读
- 可能多次读取的数据结果不一致,读取的行数据，可以写。
- 可重复读(可以添加): 解决脏读问题和不可从重复读问题,MySQL默认
- 可以重复读取，但有幻读。读写观点：读取的数据行不可写，但是可以往表中新增数据。
- 串行化:可以解决所有问题,执行效率低
- 可读，不可写。像java中的锁，写数据必须等待另一个事务结束。
参看事务的级别
- 查看当前会话隔离级别
select @@tx_isolation; - 查看系统当前隔离级别
select @@global.tx_isolation;
- 查看当前会话隔离级别
设置当前会话隔离级别
set session transaction isolatin level repeatable read;
设置系统当前隔离级别
set global transaction isolation level repeatable read;
数据库的设计
数据库设计的含义
- 查询和项目的(基础)基石,数据库的优化的前提就是数据库的设计.
数据库设计步骤
- 需求分析
- 概念结构设计:E-R模型图
- 关系模型: 将ER图转换为表,设置外模式
- 物理设计:
- 调试
- 运行和维护
数据库设计的范式
- 1NF: 所有域都原子性的,域中的数据不可分割
- 2NF: 非主键字段必须与组件相关(每张表只描述一列事务),而不能与主键部分相关(联合主键),降低数据的冗余
- 3NF:所有的非主键必须与主键直接相关,非主键字段之间不直接相关
E-R图(实体关系图)
客观世界的抽象的模型展示
实体: 矩形框
关系: 菱形框
属性: 椭圆形框
连线:
实体之间的关系
1对1: 个人信息 —- 档案
- 外键添加唯一约束
- 主键作外键
1对多(多对1):
- 在多的一方添加外键
多对多:
- 添加第三张关系表
- 设置联合主键和外键
- 将多对多的关系独立出一张表
- 表里面联合主键和二个表的主键作为外键
数据库的优化
sql 优化
尽量减少* 的使用(* 转换为列名, 然后在查询)
索引失效的情况
索引列不要使用 is not null /is null
索引列上不要使用函数
索引列上不要计算
索引列不要使用not(!= / <>)
索引列不要使用or(union 替换)
索引列不要使用包含(like ‘%s%’)
用exists 替代 in ,用not exists 代替not in
二者的选择:
- exists 先执行主查询
- in先执行的子查询
- 先过滤后关联
备份和还原
- 备份
1 | mysqldump -uroot -proot dbname > d:/back.sql |
还原
1
mysql -u root -p < c:/bac.sql
- 常用命令
- mysql.exe : 安装
- mysqld.exe: 安装和卸载mysql服务
- mysqld -remove mysql
- mysqld -installed
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 凉月の博客！
相关推荐
评论