基本概念
- 前面的学习中我们提到，mysql是关系型数据库，
- 所以我们要操作mysql就需要使用SQL（结构化查询语言）。
SQL规范
1. 在数据库管理系统中，SQL语句关键字不区分大小写(但建议用大写) ，参数区分大小写。建议命令大写，数据库名、数据表名、字段名统一小写，如数据库名、数据表名、字段名与关键字同名，使用反引号` ` 圈起来，避免冲突。
2. SQL语句可单行或多行书写，默认以英文分号（;）结尾，关键词不能跨多行或简写。
3. 字符串跟日期类型的值都要以 单引号括起来，单词之间需要使用半角的空格隔开。
4. 用空格和缩进来提高SQL语句的可读性。
SELECT * FROM table_name
WHERE name="moluo";
注释
第一种：
-- 单行注释
第二种：
/*
多行注释
*/
第三种：
# mysql支持单行注释
SQL类型
根据不同的用途，SQL语句通常分3大类型：
1、数据定义语言（Data Definition Language，DDL）
用于创建或删除数据库以及数据表的语句，DDL包含以下几种指令：
CREATE: 创建数据库和表等对象
DROP: 删除数据库和表等对象
ALTER: 修改数据库和表等对象的结构
2、数据操纵语言（Data Manipulation Language，DML）
用于对数据表中的数据进行增删查改的。
SELECT: 查询表中的数据
INSERT: 向表中插入新数据
UPDATE: 变更表中的数据
DELETE: 删除表中的数据
3、数据控制语言（Data Control Language，DCL）
用于对控制数据库的操作权限的，包括用户权限以及数据操作权限。
COMMIT: 确认对数据库中的数据进行的变更
ROLLBACK: 取消对数据库中的数据进行的变更
GRANT: 赋予用户操作权限
REMOVE: 取消用户的操作权限
常用命令
| 命令 | 描述 |
|---|---|
| help | 查看系统帮助想你想 |
| status | 查看数据库管理系统的状态信息 |
| exit | 退出数据库终端连接 |
| quit | 退出数据库终端连接 |
| \c | 当打错命令了，想换行重新写时可以在错误命令后面跟着\c回车 |
数据库操作
数据库基础
创建数据库
在磁盘上创建一个存储数据表的文件夹。
create database [if not exists] 数据库名 [character set 编码字符集];
注意：mysql中的编码字符集中utf-8，要换成utf8mb4。SQL语句中的中括号部分表示可选。
查看数据库
show databases; -- 查看所有数据库
show databases like '%test%'; -- 查看名字中包含test的数据库
show create database 数据库名; -- 查看数据库的建库sql语句
修改数据库
alter database 数据库名 [character set 编码字符集];
删除数据库
drop database [if exists] 数据库名;
使用数据库
use 数据库名; -- 切换数据库
select database(); -- 查看当前使用的数据库
- 切换数据库 注意：进入到某个数据库后没办法再退回之前状态，但可以通过use进行切换
特别注意：
使用 DROP DATABASE 命令时要非常谨慎，
- 在执行该命令后，MySQL 不会给出任何提示确认信息。
- DROP DATABASE 删除数据库后，数据库中存储的所有数据表和数据也将一同被删除，而且不能恢复。
- 因此最好在删除数据库之前先将数据库进行备份。
小结
-- 1.创建数据库（在磁盘上创建一个对应的文件夹）
create database [if not exists] db_name [character set xxx]
-- 2.查看数据库
show databases; -- 查看所有数据库
show create database db_name; -- 查看数据库的创建方式
-- 3.修改数据库
alter database db_name [character set xxx]
-- 4.删除数据库
drop database [if exists] db_name;
-- 5.使用数据库
use db_name; -- 切换数据库 注意：进入到某个数据库后没办法再退回之前状态，但可以通过use进行切换
select database(); -- 查看当前使用的数据库
数据表操作
数据表就相当于存储数据的特殊文件，数据表中的一条记录就相当于普通文件的一行内容。
moluo, True, 18, 2000, 广东, 计算机科学与技术, python开发
xiaohong, False, 17, 2001, 广西, 舞蹈学, 音乐老师
与普通文件不同的是，数据表是二维的表格结构。
创建数据表
数据表就相当于文件，文件有文件名，自然地，数据表也要有表名。同样道理，数据表中的一条记录就相当于文件的一行内容。只是不同的是，数据表需要定义表头（上图中的首行），称为表的字段名。而且因为数据库的存储数据更加科学、严谨，所以需要创建表时要给每一个字段设置数据类型以及字段约束（完整性约束条件）。
create table [if not exists] 表名 (
字段名1 数据类型[ ( 存储空间 ) 字段约束 ],
字段名2 数据类型[ ( 存储空间 ) 字段约束 ],
字段名3 数据类型[ ( 存储空间 ) 字段约束 ],
.....
字段名n 数据类型[ ( 存储空间 ) 字段约束 ],
primary key(一个 或 多个 字段名) -- 注意，最后一段定义语句，不能有英文逗号的出现，否则报错。
) [engine = 存储引擎 character set 字符集];
- 注意：
- 上面SQL语句中，小括号中的定义字段语句后面必须以英文逗号结尾，而最后一个字段的定义语句不能有英文逗号出现，否则报错。
- 在同一张数据表中，字段名是不能相同，否则报错！
- 创建数据表的SQL语句中，存储空间和字段约束是选填的，而字段名和数据类型则是必须填写的。
创建班级表
-- mysql中创建数据表要以 create table `表名`
-- 表的字段信息必须写在 ( ) 小括号里面
create table classes (
-- 建议一行一个字段，id 就是字段名
-- int 表示设置字段值要以整数的格式保存到硬盘中，
-- auto_increment表示当前字段值在每次新增数据时自动+1作为值保存
-- primary key，mysql中叫主键，表示用于区分一个数据表中不同行的数据的唯一性，同时还具备加快查询速度的作用
-- 注意：auto_increment与primary key 一般是配合使用的，对应的字段名一般也叫id，而且在一个数据表中只有一个字段能使用auto_increment primary key进行设置。
id int auto_increment primary key,
-- 字段名：name
-- varchar(10) 表示当前name这一列可以存储的数据是字符串格式，并且最多只能存10个字符
name varchar(10),
-- 字段名：address
-- varchar(100) 表示adderss这一列可以存储的数据是字符串格式，并且最多只能存100个字符
address varchar(100),
-- 字段名：total
-- int 表示当前total这一列的数据只能是整数，而且一个数据表中，整数的最大范围只能是42亿
total int
);
- 上面的SQL语句就相当于创建了一个表格。
| id | name | address | total |
|---|---|---|---|
创建学生表
create table student(
id int auto_increment, -- 字段名：id，数据类型：int整型，auto_increment整数自动增长+1
name varchar(10), -- 字段名：name， 数据类型：varchar字符串（长度限制最多10个字符）
sex int default 1, -- 字段名：sex，数据类型：int整型，默认值（default）：1 相当于True
classes int, -- 字段名：classes， 数据类型：int整型，
age int, -- 字段名：age，数据类型：int整数,
description text, -- 字段名：description，数据类型：text文本
primary key (id) -- 设置主键(id) 每个表必须都有主键，用以区分不同行的数据
);
练习：
- 假设现在我们有一个课程表(courses)，
- 里面需要保存课程编号（id），课程名（cource），授课老师（lecturer），教室（address）。
create table courses (
id int auto_increment primary key comment "课程编号",
course varchar(50) comment "课程名称",
lecturer int comment "讲师编号",
address int comment "教室编号"
);
查看数据表
查看所有数据表
- 列出当前数据库中所有的数据表
- 语法：
show tables;
查看表结构
- 以表格形式列出当前数据表的结构信息
- 语法：
-- 方式1：简单查看
describe 表名;
desc 表名; -- desc是describe的缩写
-- 方式2：详细查看
describe 表名;
desc 表名;
查看建表语句
show create table 表名 \G;
删除数据表
- 删除表结构，并把数据一并删除，
- 使用需谨慎，强烈建议先备份后删除，或者直接改表名来代替删除操作。
drop table 表名;
重置表信息
- 保留数据表结构，但是把数据表存储的数据以及数据表的状态回滚，
- 相当于删除原表，并新建一张一模一样的空数据表。
TRUNCATE table 表名;
表记录操作
【1】添加记录
INSERT 语句有两种语法形式
- INSERT…VALUES 语句
- INSERT…SET 语句
INSERT [INTO] <表名> [ <列名1> [ , … <列名n>] ] VALUES (值1) [… , (值n) ];
- 指定需要插入数据的列名。若向表中的所有列插入数据，则全部的列名均可以省略，直接采用 INSERT<表名>VALUES(…) 即可。
- INSERT 语句后面的列名称顺序可以不是 表定义时的顺序，即插入数据时，不需要按照表定义的顺序插入，只要保证值的顺序与列字段的顺序相同就可以。
- 使用 INSERT…VALUES 语句可以向表中插入一行数据，也可以插入多行数据；
INSERT [INTO] <表名> [ <列名1> [ , … <列名n>] ] VALUES (值1…,值n), (值1…,值n), ... (值1…,值n); -- 用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。
案例：
INSERT employee (name,gender,birthday,salary,department) VALUES
("yuan",1,"1985-12-12",8000,"教学部"),
("alvin",1,"1987-08-08",5000,"保安部"),
("rain",1,"1990-06-06",20000,"销售部");
# 准备：创建一个表
# CREATE TABLE emp(
# id INT PRIMARY KEY AUTO_INCREMENT,
# name VARCHAR(20),
# gender ENUM("male","female","other"),
# age TINYINT,
# dep VARCHAR(20),
# city VARCHAR(20),
# salary DOUBLE(7,2)
# )character set=utf8;
# 插入单条表记录
# insert into user(name, age, selary)
# values
# ('lisi', 18, 100);
# 插入多条表记录
# insert into user(name, age, selary)
# values
# ('lisi', 18, 100),
# ('wan', 18, 100),
# ('wu', 18, 100);
# # 批量添加信息
# INSERT INTO emp (name,gender,age,dep,city,salary) VALUES
# ("yuan","male",24,"教学部","河北省",8000),
# ("eric","male",34,"销售部","山东省",8000),
# ("rain","male",28,"销售部","山东省",10000),
# ("alvin","female",22,"教学部","北京",9000),
# ("George", "male",24,"教学部","河北省",6000),
# ("danae", "male",32,"运营部","北京",12000),
# ("Sera", "male",38,"运营部","河北省",7000),
# ("Echo", "male",19,"运营部","河北省",9000),
# ("Abel", "female",24,"销售部","北京",9000);
【2】查询记录
标准语法：
-- 查询语法：
SELECT *|field1,filed2 ... FROM tab_name
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
-- Mysql在执行sql语句时的执行顺序：
-- from where select group by having order by
准备数据：
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
gender ENUM("male","female","other"),
age TINYINT,
dep VARCHAR(20),
city VARCHAR(20),
salary DOUBLE(7,2)
)character set=utf8;
INSERT INTO emp (name,gender,age,dep,city,salary) VALUES
("yuan","male",24,"教学部","河北省",8000),
("eric","male",34,"销售部","山东省",8000),
("rain","male",28,"销售部","山东省",10000),
("alvin","female",22,"教学部","北京",9000),
("George", "male",24,"教学部","河北省",6000),
("danae", "male",32,"运营部","北京",12000),
("Sera", "male",38,"运营部","河北省",7000),
("Echo", "male",19,"运营部","河北省",9000),
("Abel", "female",24,"销售部","北京",9000);
查询字段（select）
mysql> SELECT * FROM emp;
+----+--------+--------+------+--------+--------+----------+
| id | name | gender | age | dep | city | salary |
+----+--------+--------+------+--------+--------+----------+
| 1 | yuan | male | 24 | 教学部 | 河北省 | 8000.00 |
| 2 | eric | male | 34 | 销售部 | 山东省 | 8000.00 |
| 3 | rain | male | 28 | 销售部 | 山东省 | 10000.00 |
| 4 | alvin | female | 22 | 教学部 | 北京 | 9000.00 |
| 5 | George | male | 24 | 教学部 | 河北省 | 6000.00 |
| 6 | danae | male | 32 | 运营部 | 北京 | 12000.00 |
| 7 | Sera | male | 38 | 运营部 | 河北省 | 7000.00 |
| 8 | Echo | male | 19 | 运营部 | 河北省 | 9000.00 |
| 9 | Abel | female | 24 | 销售部 | 北京 | 9000.00 |
+----+--------+--------+------+--------+--------+----------+
mysql> SELECT name,dep,salary FROM emp;
+--------+--------+----------+
| name | dep | salary |
+--------+--------+----------+
| yuan | 教学部 | 8000.00 |
| eric | 销售部 | 8000.00 |
| rain | 销售部 | 10000.00 |
| alvin | 教学部 | 9000.00 |
| George | 教学部 | 6000.00 |
| danae | 运营部 | 12000.00 |
| Sera | 运营部 | 7000.00 |
| Echo | 运营部 | 9000.00 |
| Abel | 销售部 | 9000.00 |
+--------+--------+----------+
where语句
-- where字句中可以使用：
-- 比较运算符：
> < >= <= <> !=
between 80 and 100 值在10到20之间
in(80,90,100) 值是10或20或30
like 'yuan%'
/*
pattern可以是%或者_，
如果是%则表示任意多字符，此例如唐僧,唐国强
如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__
*/
-- 逻辑运算符
在多个条件直接可以使用逻辑运算符 and or not
-- 正则
SELECT * FROM emp WHERE emp_name REGEXP '^yu';
SELECT * FROM emp WHERE name REGEXP 'n$';
- 练习：
-- 查询年纪大于24的员工
SELECT * FROM emp WHERE age>24;
-- 查询教学部的男老师信息
SELECT * FROM emp WHERE dep="教学部" AND gender="male";
# # 查找所有员工中年龄大于30的姓名，年龄
select name,age from emp where age > 30;
## 查找所有员工中年龄在30-50之间的姓名，年龄
select name,age from emp where age between 30 and 50;
# # 查找所有员工中年龄是22,24,32的姓名，年龄 （条件较少时）
select name,age from emp where age =22 or age =24 or age = 32;
# 查找所有员工中年龄是22,24,32的姓名，年龄 （条件较多时）
select name,age from emp where age in (22,24,32);
# 查找所有员工中带a的姓名，年龄
select name,age from emp where name like '%a%';
# 查找所有以a开头的员工姓名，年龄
select name,age from emp where name like 'a%';
# 查找所有以a结尾的员工姓名，年龄
select name,age from emp where name like '%a';
# 查找所有以第二个名是a的员工姓名，年龄
select name,age from emp where name like '_a%';
order：排序
- 按指定的列进行，排序的列即可是表中的列名，也可以是select语句后指定的别名。
-- 语法：
select *|field1,field2... from tab_name order by field [Asc|Desc]
-- Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
练习：
-- 按年龄从高到低进行排序
SELECT * FROM emp ORDER BY age DESC ;
-- 按工资从低到高进行排序
SELECT * FROM emp ORDER BY salary;
-- 先按工资排序，工资相同的按年龄排序
SELECT * FROM emp ORDER BY salary,age;
# order
# 查找所有记录，以年龄升序排序 asc：升序
select * from emp order by age asc ;
# 查找所有记录，以年龄降序排序 desc：降序 （若两者年龄一样，则按照主键id升序排序）
select * from emp order by age desc ;
# 查找所有记录，以年龄降序排序 desc：降序 （附加筛选条件:（若两者年龄一样，则按照薪资 降序排序））
select * from emp order by age desc , salary desc ;
group by：分组查询
- GROUP BY 语句根据某个列对结果集进行分组。分组一般配合着聚合函数完成查询。
常用聚合(统计)函数
max()：最大值。min()：最小值。avg()：平均值。sum()：总和。count()：个数。
在MySQL的SQL执行逻辑中，where条件必须放在group by前面！也就是先通过where条件将结果查询出来，再交给group by去分组，完事之后进行统计，统计之后的查询用having。
练习：
-- 查询男女员工各有多少人
SELECT gender 性别,count(*) 人数 FROM emp GROUP BY gender;
-- 查询各个部门的人数
SELECT dep 部门,count(*) 人数 FROM emp GROUP BY dep;
-- 查询每个部门最大的年龄
SELECT dep 部门,max(age) 最大年纪 FROM emp GROUP BY dep;
-- 查询每个部门年龄最大的员工姓名
SELECT * FROM emp5 WHERE age in (SELECT max(age) FROM emp5 GROUP BY dep);
-- 查询每个部门的平均工资
SELECT dep 部门,avg(salary) 最大年纪 FROM emp GROUP BY dep;
-- 查询教学部的员工最高工资:
SELECT dep,max(salary) FROM emp11 GROUP BY dep HAVING dep="教学部";
-- 查询平均薪水超过8000的部门
SELECT dep,AVG(salary) FROM emp GROUP BY dep HAVING avg(salary)>8000;
-- 查询每个组的员工姓名
SELECT dep,group_concat(name) FROM emp GROUP BY dep;
-- 查询公司一共有多少员工(可以将所有记录看成一个组)
SELECT COUNT(*) 员工总人数 FROM emp;
limit：记录条数限制
SELECT * from emp limit 1;
SELECT * from emp limit 2,5; -- 跳过前两条显示接下来的五条纪录
SELECT * from emp limit 2,2;
# limit
# 限制取出的数据为前两条
select * from emp limit 2;
# 限制取出的数据为前两条的后两条（连续取）
select * from emp limit 2,2;
distinct：查询去重
SELECT distinct salary from emp order by salary;
【3】更新记录
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
# 表记录更新
# 先查询所有年龄大于 30 的数据。
select * from emp where age > 30;
# 再对年龄大于 30 的数据进行更新
update emp set salary = 2000 where age >30;
【4】删除记录
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
# 表记录删除
# 先查找表内所有的姓名
select name from emp ;
# 再删除不想要的姓名
delete from emp where name = "Abel";
<表名>：指定要删除数据的表名。ORDER BY子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。WHERE子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。LIMIT子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
关联表
一对多关系为关系数据库中两个表之间的一种关系，该关系中第一个表中的单个行可以与第二个表中的一个或多个行相关，但第二个表中的一个行只可以与第一个表中的一个行相关。
-- 书籍表
CREATE TABLE book(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(32),
price DOUBLE(5,2),
pub_id INT NOT NULL
)ENGINE=INNODB CHARSET=utf8;
-- 出版社表
CREATE TABLE publisher(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32),
email VARCHAR(32),
addr VARCHAR(32)
)ENGINE=INNODB CHARSET=utf8;
-- 插入数据
INSERT INTO book(title,price,pub_id) VALUES
('西游记',15,1),
('三国演义',45,2),
('红楼梦',66,3),
('水浒传',21,2),
('红与黑',67,3),
('乱世佳人',44,6),
('飘',56,1),
('放风筝的人',78,3);
INSERT INTO publisher(id,name,email,addr) VALUES
(1,'清华出版社',"123","bj"),
(2,'北大出版社',"234","bj"),
(3,'机械工业出版社',"345","nj"),
(4,'邮电出版社',"456","nj"),
(5,'电子工业出版社',"567","bj"),
(6,'人民大学出版社',"678","bj");
mysql> select * from book;
+----+------------+-------+--------+
| id | title | price | pub_id |
+----+------------+-------+--------+
| 1 | 西游记 | 15.00 | 1 |
| 2 | 三国演义 | 45.00 | 2 |
| 3 | 红楼梦 | 66.00 | 3 |
| 4 | 水浒传 | 21.00 | 2 |
| 5 | 红与黑 | 67.00 | 3 |
| 6 | 乱世佳人 | 44.00 | 6 |
| 7 | 飘 | 56.00 | 1 |
| 8 | 放风筝的人 | 78.00 | 3 |
+----+------------+-------+--------+
8 rows in set (0.00 sec)
mysql> select * from publisher;
+----+----------------+-------+------+
| id | name | email | addr |
+----+----------------+-------+------+
| 1 | 清华出版社 | 123 | bj |
| 2 | 北大出版社 | 234 | bj |
| 3 | 机械工业出版社 | 345 | nj |
| 4 | 邮电出版社 | 456 | nj |
| 5 | 电子工业出版社 | 567 | bj |
| 6 | 人民大学出版社 | 678 | bj |
+----+----------------+-------+------+
6 rows in set (0.00 sec)