目录
客户机Navicat Premium 16激活教程激活
补充：查看mysql是否开启日志记录，以及历史命令
show variables like ‘log_%’; //查看是否开启log_bin
SET GLOBAL log_output = ‘TABLE’;SET GLOBAL general_log = ‘ON’; //mysql开启sql日志
SET GLOBAL log_output = ‘TABLE’; SET GLOBAL general_log = ‘OFF’; //mysql关闭sql日志
cat ~/.mysql_history //历史命令会存在当前用户目录的.mysql_history 文件内，我的是存在/root/.mysql_history里
一、基本概念
- Database：数据库，是长期储存在计算机内、有组织的、可共享的大量数据的集合。
- DBMS：数据库管理系统，是位于用户与操作系统之间的一层数据管理软件，用于科学地组织、存储和管理数据、高效地获取和维护数据。如MySQL
- DBS：数据库系统，指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统、应用系统、数据库管理员（DBA）构成。
二、MySQL
0、 安装与配置
- 官网安装包
- MySQL 8.0.19安装配置
- 遇到的问题
1）报错：‘mysql’ 不是内部或外部命令,也不是可运行的程序 或批处理文件
解决方案
2）如何解决安装MySQL后没有 my.ini 本地配置文件
解决方案
3）修改客户端服务器端编码方式为utf8
修改默认编码方式避免中文乱码
1、为什么选择MySQL数据库？
- 开放源代码
- 跨平台性
- 开源免费
- 功能强大，使用方便
2、SQL语言组成
三、MySQL基本语句
1）登陆 ： mysql -uroot -p
mysql -h 地址 -P 端口 -u 用户名 -p 密码
2）退出： exit; quit; \q
3）修改命令提示符、修改分隔符
- 修改命令提示符
例：
- 修改分隔符：DELIMITER 分隔符
4）显示当前版本，日期，用户
5）记录日志 - 记录日志：\T 日志位置
- 停止记录：\t
6）书写规范
7)
- 查看上一步操作产生的警告： SHOW WARNINGS
- 查看帮助手册： help 需要查的 > 如：help tinyint;
四、数据库常用语句(DDL)
{}必须选
|二选1
[]可选可不选
1、创建数据库
- CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [[DEFAULT] CHARACTER SET [=] charset_name];
这样在磁盘中保存数据的位置(C:\ProgramData\MySQL\MySQL Server 8.0\Data\test4)创建了一个目录
2、查看当前服务器里的数据库
- SHOW {DATABASES|SCHEMAS};
3、查看指定数据库定义(主要是查看编码方式)
- SHOW CREATE {DATABASE|SCHEMA} db_name
4、修改指定数据库编码方式
- ALTER {DATABASE|SCHEMA} [DEFAULT] CHARACTER SET [=] charset_name;
5、打开指定数据库
- USE db_name;
6、获取当前已打开的数据库名称
- SELECT DATABASE()|SCHEMA();
7、删除指定数据库
- DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;
五、数据库支持的数据类型
- 整形
- 浮点型
- 字符串型
-- 测试CHAR和VARCHAR
CREATE TABLE IF NOT EXISTS test5(
str1 CHAR(5),
str2 VARCHAR(5)
);
INSERT test5 VALUES('1','1');
INSERT test5 VALUES('12345','12345');
INSERT test5 VALUES('123456','123456');
INSERT test5 VALUES('','');
INSERT test5 VALUES('1 ','1 ');
INSERT test5 VALUES(' a',' a');
INSERT test5 VALUES('啊啊啊啊啊','麦子学院好');
CREATE TABLE test6(
str1 TEXT
);
INSERT test6 VALUES('skdfjlksdfjlksjdflkj塑料口袋精灵是看见对方离开首都基辅绿卡时间的联发科技');
-- 测试枚举类型
CREATE TABLE IF NOT EXISTS test7(
sex ENUM('男','女','保密 ')
);
INSERT test7 VALUES('男 ');
INSERT test7 VALUES('女 ');
INSERT test7 VALUES('保密');
INSERT test7 VALUES(NULL);
INSERT test7 VALUES(1);
-- 测试集合类型
CREATE TABLE IF NOT EXISTS test8(
fav SET('A','B','C','D')
);
INSERT test8 VALUES('A,C,D');
INSERT test8 VALUES('D,B,A');
INSERT test8 VALUES(3);
INSERT test8 VALUES(15);
- 日期时间型
-- 测试日期时间
CREATE TABLE IF NOT EXISTS test9(
birth YEAR
);
INSERT test9 VALUES(1901);
INSERT test9 VALUES(2155);
INSERT test9 VALUES(2156);
- 二进制类型
怎么选择？
例：
六、存储引擎相关内容
1、什么是存储引擎？
存储引擎就是指表的类型，数据库的存储类型决定了表在计算机中的存储方式，用户可以根据不同的存储方式、是否进行事务处理等选择合适的存储引擎。
2、如何查看MySQL的存储引擎？
-
查看MySQL支持的存储引擎：SHOW ENGINES;
更美观的显示可以: SHOW ENGINES \G;
-
查看显示支持的存储引擎信息：SHOW VARIABLES LIKE ‘have%’;
-
查看默认存储引擎：SHOW VARIABLES LIKE ‘%storage_engine%’;
3、MySQL常用存储引擎及特点
- Innodb
- MyISAM
- MEMORY
常用存储引擎特点
七、数据表的相关操作
1、什么是数据表
2、如何创建表
具体如下：
CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY| UNIQUE [KEY]] [AUTO_INCREMENT]
)ENGINE=INNODB CHARSET=UTF8 AUTO_INCREMENT=100;
创建表时首先要打开指定数据库。以下为创建学员表示例
# 创建
CREATE DATABASE IF NOT EXISTS orange DEFAULT CHARACTER SET 'UTF8';
USE orange;
# 创建学员表
-- 编号 id
-- 用户名 username
-- 年龄 age
-- 性别 sex
-- 邮箱 email
-- 地址 addr
-- 生日 birth
-- 薪水 salary
-- 电话 tel
-- 是否结婚 married
-- 注意：当需要输入中文时，需要临时转换编码方式 SET NAMES GBK;
-- 字段注释通过COMMENT+注释内容
SET NAMES GBK;
CREATE TABLE IF NOT EXISTS `user1`(
id SMALLINT,
username VARCHAR(20),
age TINYINT,
sex ENUM('男','女','保密'),
email VARCHAR(50),
addr VARCHAR(200),
birth YEAR,
salary FLOAT(8,2),
tel INT,
married TINYINT(1) COMMENT '0代表未结婚，1代表结婚'
)ENGINE=INNODB CHARSET=UTF8;
3、查看数据表
- SHOW TABLES;
4、查看指定表的表结构
- DESCRIBE tbl_name；
- DESC tbl_name;
- SHOW COLUMNS FROM tbl_name;
5、向表中插入数据
- 向表中插入记录： INSERT tbl_name VALUE|VALUES(值,…);
- 查询表中所有记录：SELECT * FROM tbl_name;
６、完整性约束条件
- 无符号：UNSIGNED
- 零填充：ZEROFILL
- 完整性约束条件
- 1）主键：唯一约束条件的字段（不重复、非空字段），PRIMARY可省略，主键常创建在无意义字段上。
-- 测试主键
CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY,
username VARCHAR(20)
);
-- 查看创建表的标的定义
SHOW CREATE TABLE user1;
INSERT user1 VALUES(1,'king');
INSERT user1 VALUES(13,'QUEEN');
);
# 复合主键，多字段主键
CREATE TABLE IF NOT EXISTS user2(
id INT,
username VARCHAR(20),
card CHAR(18),
PRIMARY KEY(id,card)
);
INSERT user2 VALUES(1,'king','111');
INSERT user2 VALUES(1,'queen','112');
- AUTO_INCREMENT 测试自增长
自增长字段一定为主键，主键不一定自增长
自增长字段为整型
-- 测试AUTO_INCREMENT
CREATE TABLE IF NOT EXISTS user5(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
);
INSERT user5 VALUES(1,'KING');
INSERT user5(username) VALUES('queen1');
INSERT user5 VALUES(111,'KING1');
INSERT user5(username) VALUES('queen');
INSERT user5 VALUES(NULL,'AAAA');
INSERT user5 VALUES(DEFAULT,'AAAA');
CREATE TABLE IF NOT EXISTS user6(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
)AUTO_INCREMENT=100;
INSERT user6(username) VALUES('queen1');
- 非空约束
字段值非空
-- 测试NOT NULL
CREATE TABLE IF NOT EXISTS user7(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED
);
INSERT user7(username,password) VALUES('KING','KING');
INSERT user7(username,password,age) VALUES('KING1','KING1',12);
INSERT user7(username,password) VALUES(NULL,NULL);
- 测试默认值
常配合ＮOT NULL使用，如果没给值，则字段值为DEFAULT值
-- 测试DEFAULT
CREATE TABLE IF NOT EXISTS user8(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT '北京',
sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
);
INSERT user8(username,password) VALUES('KING','KING');
INSERT user8 VALUES(2,'QUEEN','QUEEN',29,'上海','保密');
INSERT user8 VALUES(3,'QUEEN','QUEEN',DEFAULT,DEFAULT,'保密');
- 测试唯一性约束
一个表中只有一个主键，但可以有多个唯一性约束条件。
带有UNIQUE的字段值不能重复，如用户名
-- 测试UNIQUE KEY
CREATE TABLE IF NOT EXISTS user9(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) UNIQUE
);
INSERT user9(username) VALUES('A');
INSERT user9(username,card) VALUES('B','111');
INSERT user9(username,card) VALUES('B1',NULL);
INSERT user9(username,card) VALUES('B2',NULL);
以创建用户表为例
-- 创建用户表
CREATE TABLE IF NOT EXISTS user10(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男','女','保密') DEFAULT '保密',
addr VARCHAR(200) NOT NULL DEFAULT '北京',
salary FLOAT(6,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default.jpg'
);
7、如何修改表结构
对一个表进行多次操作
-- 添加test字段删除addr字段
ALTER TABLE user10
ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,
DROP addr;
注意：删除主键时，如果字段是主键的同时是自增长的，需要先修改字段的自增长属性再删除
CREATE TABLE IF NOT EXISTS test14(
id INT UNSIGNED KEY AUTO_INCREMENT
);
ALTER TABLE test14 MODIFY id INT UNSIGNED;
ALTER TABLE user10 DROP PRIMARY KEY ;
8、删除数据表
八、数据增删改（DML）
1、INSERT 增加数据
-- 测试插入记录INSERT
CREATE TABLE IF NOT EXISTS user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
age TINYINT UNSIGNED DEFAULT 18
);
INSERT user VALUE(2,'QUEEN','QUEEN','QUEEN@QQ.COM',30);
INSERT user(username,password) VALUES('A','AAA');
-- 一次插入多条记录
INSERT user VALUES(6,'D','DDD','D@QQ.COM',35),
(18,'F','FFF','F@QQ.COM',32);
-- 通过INSERT SET形式插入记录
INSERT user SET username='maizi',password='maizixueyuan' ;
CREATE TABLE IF NOT EXISTS testUser(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE
);
-- 将查询结果插入到表中
INSERT testUser SELECT id,username FROM user;
2、DELETE 删除数据
-- 删除用户性别为男的用户，按照年龄降序排列，删除1前一条记录
DELETE FROM cms_user WHERE sex='男' ORDER BY age DESC LIMIT 1;
3、UPDATE 更新数据
-- 将用户表中所有的用户年龄更新15
UPDATE user SET age=5;
UPDATE user SET age=20,email='test@qq.com';
-- 将第一个记录的password，email，age
UPDATE user SET password='king123',email='123@qq.com',age=99
WHERE id=1;
UPDATE user SET age=age-5 WHERE id>=3;
UPDATE user SET age=DEFAULT WHERE username='A';
-- 更新用户名为4位的用户，让其已有年龄-3
UPDATE cms_user SET age=age-3 WHERE username LIKE '____';
-- 更新前3条记录，让已有年龄+10
UPDATE cms_user SET age=age+10 LIMIT 3;
UPDATE cms_user SET age=age+10 LIMIT 0,3; -- ERROR 更新删除不能设置偏移量
-- 按照id降序排列，更新前3条
UPDATE cms_user SET age=age+10 ORDER BY id DESC LIMIT 3;
九、数据查询（DQL）
1、单表查询
1.1 查询表中数据
SELECT id,sex,age,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
WHERE id>=1
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 0,2;
1.2 基本查询
-- 查询
SELECT * FROM cms_admin;
SELECT cms_admin.* FROM cms_admin;
-- 查询管理员编号和名称
SELECT id,username FROM cms_admin;
-- 表来自于哪个数据库下db_name.tbl_name 库名.表名
SELECT id,username,role FROM cms.cms_admin;
-- 字段来自于哪张表 表名.字段名
SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;
-- 给表名起别名
SELECT id,username FROM cms_admin AS a;
SELECT id,username FROM cms_admin a;
SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;
-- 给字段起别名
SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;
SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a;
1.3 WHERE条件
<=>表示=，但是它比等号多了一个功能，可查询NULL值
例：SELECT * FROM cms_user WHERE age<=>NULL;
BETWEEN AND 和IN （集合）
HAVING 对分组结果进行二次筛选
-- 查询编号在3~10之间的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;
-- 查询编号为1，3，5，7，9，11，13，100
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100,1000);
模糊查询
%:代表0个一个或者多个任意字符
_:代表1个任意字符
-- 查询姓张的用户
SELECT * FROM cms_user WHERE username LIKE '张%';
-- 查询用户名中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%in%';
SELECT * FROM cms_user WHERE username LIKE '%';
-- 查询用户名为3位的用户
SELECT * FROM cms_user WHERE username LIKE '___';
--用户名_i%
SELECT * FROM cms_user WHERE username LIKE '_I%';
SELECT * FROM cms_user WHERE username LIKE 'king';
SELECT * FROM cms_user WHERE username NOT LIKE '_I%';
-- 查询编号在5~10的用户并且用户名为4位的用户
SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';
1.4 GROUP BY 分组查询
1）
--按照字段位置分组
SELECT * FROM cms_user GROUP BY 7;
--按照多个字段分组
SELECT * FROM cms_user GROUP BY sex,proId;
-- 查询编号大于等于5的用户按照sex分组
SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;
2）
注意COUNT（字段）不统计NULL值
--查询编号,sex,用户名详情以及组中总人数按照sex分组
SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;
-- 统计表中所有记录
SELECT COUNT(*) AS totalUsers FROM cms_user;
SELECT COUNT(id) AS totalUsers FROM cms_user;
--查询编号,性别,用户名详情，组中总人数，组中最大年龄，最小年龄，
-- 平均年龄，以及年龄总和按照性别分组
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex;
-- WITH ROLLUP
SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user
GROUP BY sex WITH ROLLUP;
GROUP_CONCAT结果：
WITH ROOLUP结果
1.5 HAVING条件
对分组结果进行二次筛选
-- 查询组中人数大于2并且最大年龄大于60的
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;
1.6 ORDER BY 对显示结果排序
-- 按照id降序排列DESC 默认的是ASC
--升序
SELECT * FROM cms_user ORDER BY id ;
SELECT * FROM cms_user ORDER BY id ASC;
--降序
SELECT * FROM cms_user ORDER BY id DESC;
-- 按照年龄升序，id降序排列
--年龄相同，考虑按ID排序
SELECT * FROM cms_user ORDER BY age ASC,id DESC;
-- 实现记录随机
SELECT * FROM cms_user ORDER BY RAND();
1.7 LIMIT 限制查询结果显示条数
-- 查询表中前3条记录
-- 查询表中前3条记录
SELECT * FROM cms_user LIMIT 3;
SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;
-- 查询表中前一条记录
SELECT * FROM cms_user LIMIT 1;--显示第一条
SELECT * FROM cms_user LIMIT 0,1;--显示第一条
SELECT * FROM cms_user LIMIT 1,1;--显示第二条
SELECT * FROM cms_user LIMIT 0,5;--显示前五条
2、连接查询
2.1 JOIN ON连接查询
用于多个表之间的查询
什么是连接查询?
a.内连接查询
主要就是找两张表之间的连接条件
# 两张表之间的查询
-- 查询cms_user表中id,username,email,sex
-- 查询provinces表proName
-- 按proName分组，并得到每一组的组中人数
-- 得到用户名分组详情
--二次筛选组中人数大于等于1的
-- 按照id升序排列
-- 限制显示前四条
SELECT u.id, GROUP_CONCAT(u.username), u.email, u.sex, p.proName,COUNT(*) AS userCount
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING userCount>=1
ORDER BY u.id
LIMIT 0,4;
# 三张表之间的查询
-- cms_news id ,title
-- cms_cate cateName
-- cms_admin username,role
SELECT n.id,n.title,c.cateName,a.username,a.role
FROM cms_cate AS c
JOIN cms_news AS n
ON n.cId=c.id
JOIN cms_admin AS a
ON n.aId=a.id;
b.外连接查询
若找不到符合条件的记录，用NULL替代
-- 左外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
LEFT JOIN provinces AS p
ON u.proId=p.id;
-- 右外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
RIGHT JOIN cms_user AS u
ON u.proId=p.id;
2.2 外键
a. 什么是外键
b.设置外键的注意事项
c.创建添加删除外键
1、创建表时添加外键
外键depId服从主表中的id, 外键名称为 emp_fk_dep
CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id)
2、删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;
3、添加外键
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
– 创建部门表，员工表，要求：
– 1）希望解散某部门（主表）的时候，需要该部门的员工（子表）也不在才可以删除部门
– 2）希望无法添加垃圾数据，如员工A属于垃圾部
-- 创建部门表department(主表)
-- id depName
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
-- 创建员工表employee(子表)；并添加外键
-- id ,username ,depId
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id)
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);
-- 删除主表中的记录
DELETE FROM department WHERE id=1; # 报错
-- 删除employee中的属于1部门的人
DELETE FROM employee WHERE depId=1;
INSERT employee(username,depId) VALUES('test',11); #报错
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;
-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
d. 外键的约束条件
希望对父表进行操作时，子表也会改变
1、删除和更新的级联操作，更新删除父表的某一记录，子表对应记录也更新删除
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE2、SET NULL 删除父表某一部门，子表对应部门号变为NULL
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
2.3、UNION 联合查询
查询两个表的内容并合并到一起。
-- UNION
SELECT username FROM employee UNION SELECT username FROM cms_user;
-- UNION ALL
SELECT username FROM employee UNION ALL SELECT username FROM cms_user;
SELECT id,username FROM employee UNION ALL SELECT username,age FROM cms_user;
3、子查询
什么是子查询?
3.1 什么情况会引发子查询?
-- 1、由[NOT] IN引发的子查询
SELECT id FROM department;
SELECT id,username FROM employee WHERE depId IN(1,2,3,4);
-- 嵌套语句，由[NOT] IN引发的子查询
SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department);
-- 创建学员表student
-- id username score
CREATE TABLE IF NOT EXISTS student(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
score TINYINT UNSIGNED) ENGINE=INNODB;
INSERT student(username,score) VALUES('king',95),
('king1',35),
('king2',45),
('king3',55),
('king4',65),
('king5',75),
('king6',80),
('king7',90),
('king8',25);
-- 创建奖学金scholarship
-- id ,level
CREATE TABLE IF NOT EXISTS scholarship(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
level TINYINT UNSIGNED
);
INSERT scholarship(level) VALUES(90),(80),(70);
--2、由比较运算符引发的子查询
-- 查询获得1等奖学金的学员有
SELECT level FROM scholarship WHERE id=1;
SELECT id,username, score FROM student WHERE score>=90;
--嵌套
SELECT id,username, score FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);
--3、EXISTS引发的子查询
如果EXISTS内层语句为真，外层语句执行，反之不执行
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);
--4、由ANY ALL引发的子查询
-- 查询所有获得奖学金的学员
SELECT id,username FROM student WHERE score>= ANY(SELECT level FROM scholarship);
-- 查询所有学员中获得一等奖学金的学员
SELECT id,username,score FROM student WHERE score >=ALL(SELECT level FROM scholarship);
-- 查询学员表中没有获得奖学金的学员
SELECT id,username,score FROM student WHERE score<ALL(SELECT level FROM scholarship);
-- =ANY 相当于IN
SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score IN(SELECT level FROM scholarship);
-- <> ALL 相当于NOT IN
SELECT id,username,score FROM student WHERE score NOT IN(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score <> ALL(SELECT level FROM scholarship);
3.2 将查询结果写入数据表中
CREATE TABLE test1 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
);
INSERT test1(id,num) SELECT id,score FROM student;
3.3 创建数据表的同时将查询结果写入数据表中
CREATE TABLE test2 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
)SELECT id,score FROM student;
4、正则表达式查询
常用匹配字符：
-- ^匹配字符开始的部分
-- 查询用户名以t开始的用户
SELECT * FROM cms_user WHERE username REGEXP '^t';
-- $匹配字符串结尾的部分
SELECT * FROM cms_user WHERE username REGEXP 'g$';
-- .代表任意字符
SELECT * FROM cms_user WHERE username REGEXP '.';
SELECT * FROM cms_user WHERE username REGEXP 'r..g';
SELECT * FROM cms_user WHERE username LIKE 'r__g';
-- [字符集合] [lto]
SELECT * FROM cms_user WHERE username REGEXP '[lto]';
SELECT * FROM cms_user WHERE username REGEXP '[a-k]';
-- [^字符集合] 除了字符集合中的内容
SELECT * FROM cms_user WHERE username REGEXP '[^l]';# 注意只能排除用户名全为l的。
SELECT * FROM cms_user WHERE username REGEXP '[^a-m]';
--S1|S2匹配S1或S2的任意一个字符
SELECT * FROM cms_user WHERE username REGEXP 'ng|qu|te';
--匹配que,e出现0次或多次
SELECT * FROM cms_user WHERE username REGEXP 'que*';
--匹配t，t出现一次或多次
SELECT * FROM cms_user WHERE username REGEXP 't+';
--匹配que,e出现2次
SELECT * FROM cms_user WHERE username REGEXP 'que{2}';
SELECT * FROM cms_user WHERE username REGEXP 'que{2}'; # e出现1-3次
十、Mysql常见运算符
1、算数运算符
2、比较运算符
SELECT 1=2;
SELECT id,username,score>90 FROM student;
3、逻辑运算符
4、运算符优先级
十一、数学函数库
1、数学函数
SELECT username, CEIL(salary) FROM cms_user;
SELECT TRUNCATE(3.1414,2);
2、字符串函数
SELECT id, CONCAT(username,‘_’) FROM cms_user;
3、日期时间函数
4、条件判断函数
SELECT id, username, score, IF(score>60,‘及格’,‘不及格’) AS level FROM student;
SELECT id, username,score,CASE WHEN score>90 THEN ‘一等’ WHEN score >80 THEN ‘二等’ ELSE ‘三等’ END AS level FROM student;
5、系统信息函数
6、加密函数
7、其它常用函数
十二、Mysql的索引
什么是索引？
1、索引的分类
2、创建表时创建索引
3、在已存在的表上添加索引
4、删除索引
-- 创建普通索引
CREATE TABLE test4(
id TINYINT UNSIGNED,
username VARCHAR(20),
INDEX in_id(id),
KEY in_username(username)
);
DROP INDEX in_id ON test4;
DROP INDEX in_username ON test4;
CREATE INDEX in_id ON test4(id);
ALTER TABLE test4 ADD INDEX in_username(username);
-- 创建唯一索引
CREATE TABLE test5(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) NOT NULL,
UNIQUE KEY uni_card(card)
);
ALTER TABLE test5 DROP INDEX uni_card;
DROP INDEX username ON test5;
CREATE UNIQUE INDEX uni_username ON test5(username);
ALTER TABLE test5 ADD UNIQUE INDEX uni_card(card);
-- 创建全文索引
CREATE TABLE test6(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
userDesc VARCHAR(20) NOT NULL,
FULLTEXT INDEX full_userDesc(userDesc)
);
DROP INDEX full_userDesc ON test6;
CREATE FULLTEXT INDEX full_userDesc ON test6(userDesc);
-- 创建单列索引
CREATE TABLE test7(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX in_test1(test1)
);
-- 创建多列索引
CREATE TABLE test8(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX mul_t1_t2_t3(test1,test2,test3)
);
ALTER TABLE test8 DROP INDEX mul_t1_t2_t3;
ALTER TABLE test8 ADD INDEX mul_ti_t2_t3(test1,test2,test3);
CREATE TABLE test9(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
UNIQUE KEY mul_t1_t2_t3(test1,test2,test3)
);
-- 创建空间索引
CREATE TABLE test10(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test)
)ENGINE=MyISAM;
DROP INDEX spa_test ON test10;
CREATE SPATIAL INDEX spa_test ON test10(test);
十三、数据库图形化管理工具
扫一扫
1532
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
