MySql基础
1 数据库操作
1.1 查看数据库
show databases;
1.2 查看数据库定义
show create database 数据库名;
1.3 创建数据库
create database 数据库名;
1.4 删除数据库
drop database 数据库名;
1.5 修改数据库
alter database 数据库名 character set utf8mb4;
1.6 选择数据库
创建表前需要选择数据库
use 数据库名;
2 表创建
2.1 创建表
create table 表名 ( 字段名1， 数据类型 【列级别约束条件】【默认值】， 字段名2，数据类型 【列级别约束条件】 【默认值】， ... 【表级别约束条件】 ）;
use testdb; create table tb_emp1 ( id int(11), name varchar(26), deptId int(11), salary float );
2.2 查看表
show tables;
2.3 查看表结构
describe 表名； 或 desc 表名
2.4 查看创建表的语句
show create table 表名；
2.5 主键约束
主键，主码，是表中一列或多列的组合，主键约束（Primary Key Constraint）要求主键列数据唯一，并且不为空。
主键能唯 一标识表中的一条记录，可以加快数据库查询速度。
主键分为单字段主键和多字段联合主键
单字段主键
use testdb; create table tb_emp1 ( id int(11) PRIMARY KEY, name varchar(26), deptId int(11), salary float ); 或 use testdb; create table tb_emp1 ( id int(11), name varchar(26), deptId int(11), salary float, PRIMARY KEY(id) );
多字段联合主键
create table tb_emp1 ( id int(11), name varchar(26), deptId int(11), salary float, primary key(id, name) );
2.6 外键约束
外键用来在两个表的数据之间建立链接，它可以是一列或者多列，一个表可以有一个或多个外键。
一个表的外键可以为空，但若不为空，则每一个外键值必须等于另一个表中的主键的某个值。
外键是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。
外键主要作用是保证 数据引用的完整性，定义外键后，不允许 删除在另一个表中具有关联关系的行
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表是主表
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表是从表
create table tb_dept1 ( id int(11) primary key , name varchar(22) not null, location varchar(50) ); create table tb_emp1( id int(11), name varchar(26), deptId int(11), salary float, primary key(id, name), CONSTRAINT fk_emp_dept1 FOREIGN KEY (deptId) REFERENCES tb_dept1(id) );
外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update
外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对
-- 应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的
-- on update/on delete子句
-----------------innodb支持的四种方式---------------------------------------
-----cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录
-----外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除--------
FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE CASCADE
------set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null
-- 要注意子表的外键列不能为not null
FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE SET NULL
------Restrict方式 :拒绝对父表进行删除更新操作(了解)
------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键
-- 进行update/delete操作（了解）
2.7 非空约束
非空约束（Not Null Constraint)指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。
create table tb_dept1 ( id int(11) primary key , name varchar(22) not null, location varchar(50) );
2.8 唯一性约束
唯一性约束要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
唯一性约束和主键约束的区别：一个表中可以有多个字段声明为唯一性约束，但是只能有一个主键约束 ；
主键列不允许有空值，唯一约束的字段允许有空值。
create table tb_emp2( id int(11), name varchar(26) UNIQUE, deptId int(11), salary float, primary key(id, name), constraint fk_emp_dept1 FOREIGN KEY (deptId) REFERENCES tb_dept1(id) ); 或 create table tb_emp2( id int(11), name varchar(26), deptId int(11), salary float, primary key(id, name), constraint fk_emp_dept1 FOREIGN KEY (deptId) REFERENCES tb_dept1(id), CONSTRAINT un_name UNIQUE(name) );
2.9 默认约束
用于指定某列的默认值, 如设置默认性别为男
create table tb_emp2( id int(11), name varchar(26), deptId int(11), gender varchar(22) DEFAULT "男", salary float, primary key(id, name), constraint fk_emp_dept1 FOREIGN KEY (deptId) REFERENCES tb_dept1(id), CONSTRAINT un_name UNIQUE(name) );
2.10 属性值自动增加
由系统自动生成字段的主键值，每新增一条记录，字段值自动加1，一个表只能有一个字段使用auto_increment约束，且该字段必须为主键的一部分。
create table tb_emp2( id int(11) auto_increment, name varchar(26), deptId int(11), gender varchar(22) DEFAULT "男", salary float, primary key(id, name), constraint fk_emp_dept1 FOREIGN KEY (deptId) REFERENCES tb_dept1(id), CONSTRAINT un_name UNIQUE(name) );
3 表修改
3.1 改表名
alter table 旧表名 rename 新表名
3.2 修改字段类型
alter table 表名 modify 字段名 数据类型
3.3 修改字段名
alter table 表名 change 旧字段名 新字段名 新数据类型
3.4 添加字段
alter table 表名 add 新字段名 数据类型 【约束条件】【FIRST|AFTER 以存在字段名】;
FIRST或AFTER已存在字段名的相对位置增加新字段名
如果不指定在最后一列添加
3.5 删除字段
alter table 表名 drop 字段名;
3.6 修改字段排列位置
alter table 表名 modify 字段1 数据类型 first|after 字段2
3.7 修改表的存储引擎
alter table 表名 engine=更改后的存储引擎名；
不是每个表都 可以做任意选择存储引擎
外键约束不能跨引擎使用
3.8 删除表的外键约束
alter table 表名 drop foreign key 外键约束名；
3.9 删除数据表
删除数据表会把表从数据库中删除，删除时表的定义和表中所有的数据均会被删除
drop table [if exists] 表1，表2，...表n;
表N表示要删除的表名，可以同时删除多个表，如果表不存在，会报错。但是加上 if exists 删除不存在的表不会报错，只会警告
有关联关系的表只能删除从表，不能删除父表。
如果想要删除父表，必须先删除从表的外键约束，再删除父表。
4 查询数据
4.1 基本查询语句
查询基本语句为select，select语句基本格式是
SELECT {*|字段列表} [ FROM 表1，表2... [WHERE 条件表达式] [GROUTP BY 字段名] [HAVING 条件表达式] [ORDER BY 字段名] [LIMIT [偏移]，行数] ]
单表基本查询语句
SELECT 字段1，字段2.. FROM 表名 WHERE 条件
查询结果不重复可以使用， DISTINCT是应用于所有查询字段，不是它后面的指定列，如查询3个字段，id,name,age如果不同记录的这三个字段 的组合值不对，都会被查出来
SELECT DISTINCT 字段名 FROM 表名
4.2 排序
SELECT 字段1，字段2.. FROM 表名 ORDER BY 字段名 ASC或DESC
4.3 分组查询
分组查询是对数据按照某个或多个字段进行分组
SELECT 字段1，字段2.. FROM 表名 GROUP BY 字段名 HAVING 条件表达式
GROUP BY通常和集合函数一起使用，MAX，MIN, COUNT, SUM, AVG
SELECT dep_id， COUNT(*) as Total from tb_emp GROUP BY dep_id
dep_id是部分ID，根据部门分组，统计每个部门的员工数量
GROUP_CONCAT可以把分组的各个字段值拼起来显示
SELECT dep_id, GROUP_CONCAT(name) from tb_emp GROUP BY dep_id
这个语句会把所每个部门所有员工名字按逗号分割并拼接起来
HAVING关键字可以限定记录所满足的条件，只有满足条件的分组才会显示
SELECT dep_id, GROUP_CONCAT(name) from tb_emp GROUP BY dep_id HAVING COUNT(name) >1
会显示部分人数大于1的名字的拼接
HAVING与WHERE都是用来过滤数据区别是HAVING在数据分组之后进行过滤，而HWERE在分组之前用来选择记录，WHERE排除的记录不会出现在分组中
WITH ROLLUP 可以在所有查询出的分组记录之后增加一条记录，记录计算查询 所有记录的总合，统计记录的数量，这个和ORDEY BY不能同时使用
SELECT dep_id, COUNT(*) from tb_emp GROUP BY dep_id WITH ROLLUP
不但统计出了每个部门员工的数量，还增加了一行，显示所有员工的数量
多字段分组，GROUP BY可以先按第一个字段分组后，再第一个字段值相同的记录中再根据第2个字段分组
4.4 LIMIT限制查询结果数量
SELECT 字段1，字段2.. FROM 表名 WHERE 条件 LIMIT[位置偏移量，] 行数
位置偏移，不指定默认从第一条记录开始，第一条记录的偏移量是0，第二个参数返回记录的条数
4.5 集合函数
| 函数名 | 说明 | 分组使用（GROUP BY) |
|---|---|---|
| AVG() | 计算指定字段的平均值 | 计算每个分组的平均值 |
| COUNT() | COUNT(*)返回总行数COUNT(字段名)忽略空值的行 | 统计不同分组中的记录总数 |
| SUM() | 求记录中指定字段的和 | 计算每个分组中指定字段的和 |
| MAX() | 指定字段的最大值 | 求每个分组中的最大值 |
| MIN() | 指定字段的最小值 | 求每个分组中的最小值 |
4.6 连接查询
SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）、SQL LEFT JOIN、SQL RIGHT JOIN、SQL FULL JOIN，其中前一种是内连接，后三种是外链接。
假设我们有两张表，Table A是左边的表，Table B是右边的表。
| id | name |
|---|---|
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
| id | address |
|---|---|
| 1 | 北京 |
| 5 | 上海 |
| 3 | 广州 |
| 6 | 深圳 |
4.6.1 INNER JOIN
内连接是最常见的一种连接，只连接匹配的行。
SELECT 字段名 FROM 表1 INNER JOIN 表2 ON 表1.字段=表2.字段 AND 条件达式
注释：INNER JOIN与JOIN是相同
INNER JOIN产生的结果集中，是1和2的交集。
select * from TableA inner join TableB on TableA.id=TableB.id
执行以上SQL输出结果如下：
| id | name | address |
|---|---|---|
| 1 | 张三 | 北京 |
| 3 | 王五 | 广州 |
4.6.2 LEFT JOIN
LEFT JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。
LEFT JOIN 语法
SELECT 字段名 FROM 表1 LEFT JOIN 表2 ON 表1.字段=表2.字段 AND 条件达式
注释：在某些数据库中，LEFT JOIN 称为LEFT OUTER JOIN
LEFT JOIN产生表1的完全集，而2表中匹配的则有值，没有匹配的则以null值取代。
select * from TableA left join TableB on TableA.id=TableB.id
执行以上SQL输出结果如下：
| id | name | address |
|---|---|---|
| 1 | 张三 | 北京 |
| 2 | 李四 | null |
| 3 | 王五 | 广州 |
| 4 | 赵六 | null |
4.6.3 RIGHT JOIN
RIGHT JOIN返回右表的全部行和左表满足ON条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。
RIGHT JOIN语法
SELECT 字段名 FROM 表1 RIGHT JOIN 表2 ON 表1.字段=表2.字段 AND 条件达式
RIGHT JOIN产生表2的完全集，而1表中匹配的则有值，没有匹配的则以null值取代。
select * from TableA right join TableB on TableA.id=TableB.id
执行以上SQL输出结果如下：
| id | name | address |
|---|---|---|
| 1 | 张三 | 北京 |
| 5 | null | 上海 |
| 3 | 王五 | 广州 |
| 6 | null | 深圳 |
4.7 子查询
子查询常用 的操作符有ANY(SOME), ALL, IN, EXISTS，子查询可以添加到SELECT，UPDATE,DELETE语句中，可以进行多层嵌套
ANY SOME查询，表示子查询中的任意一个值满足条件，就可以做为主查询的条件判断
SELECT 字段1，字段2 .. FROM 表1 WHERE 字段1 > ANY ( SELECT 表2.字段1 FROM 表2 ... )
ALL子查询，要求必须子查询的任意一个值都满足条件，就可以做为主查询的条件判断
SELECT 字段1，字段2 .. FROM 表1 WHERE 字段1 <= SOME ( SELECT 表2.字段1 FROM 表2 ... )
EXISTS子查询，判断子查询是否有结果，如果至少有一行数据返回，则执行主查询，否则，主查询不执行
SELECT 字段1，字段2 .. FROM 表1 WHERE 字段1条件表达式 AND [NOT]EXISTS ( SELECT 表2.字段1 FROM 表2 ... )
IN子查询，子查询返回一个数据列，主查询判断字段值是不是在这个数据列中
SELECT 字段1，字段2 .. FROM 表1 WHERE 字段1 [NOT]IN ( SELECT 表2.字段1 FROM 表2 ... )
4.8 合并查询结果
UNION可以把多条SELECT语句的结果合成一个，放在一张表中显示，但是必须保证对应的字段数和数据类型是相同的
不加ALL删除重复记录，加ALL显示所有记录
SELECT 字段1，字段2 .. FROM 表1 UNION[ALL] SELECT 字段1，字段2 .. FROM 表1
4.9 为表和字段取别名
表名 [AS] 表别名
字段名 [AS] 字段别名
4.10 使用正则表达式查询
SELECT 字段1，字段2 .. FROM 表1 WHERE 字段1 REGEXP '正则表达式'
5 插入、更新、删除
5.1 插入一条记录
INSERT INTO 表名 (字段1， 字段2 ..) VALUES (值1， 值2...), (值1， 值2...), (值1， 值2...)... INSERT INTO 表名 SET 字段1=值1，字段2=值2 ..
5.2 更新记录
UPDATE 表名 SET 字段1=值1，字段2=值2 .. WHERE 条件表达式
5.3 删除记录
DELETE FROM 表名 WHERE 条件表达式 #删除全部 DELETE FROM 表名 # 删除表，再重新创建 TRUNCATE table 表名;
浙公网安备 33010602011771号