代码拉取完成，页面将自动刷新
| title | author | date |
|---|---|---|
mysql学习笔记 |
lczmx |
2021年6月3日 |
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品，是最流行的关系型数据库管理系统之一。
端口是3306。
;结尾，支持多行输入--
/* .... */
CREATE DATABASE <数据库名称>;
CREATE DATABASE <数据库名称> CHARACTER SET <编码方式>;
CREATE DATABASE IF NOT EXISTS <数据库名称>;
SHOW WARNINGS；查看。 SHOW DATABASES;
SELECT DATABASE();
SHOW CREATE DATABASE <数据库名称>;
USE <数据库名称>;
ALTER DATABASE <数据库名称> CHARACTER SET <编码方式>;
RENAME DATABASE <旧名称> TO <新名称>;
-- 1 创建新的数据库"data"
CREATE DATABASE data;
-- 2 使用RENAME TABLE命令修改表名，将表移动到新的库里
RENAME TABLE user.table1 TO data.table1;
-- 假如有多个表的话，应该重复使用RENAME命令
-- 3 完成后删除旧库”user“
DROP DATABASE user;
#!/bin/bash
mysql -uroot -p<密码> -e 'create database if not exists <新数据库名称>;'
list_table=$(mysql -uroot -p<密码> -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='<旧数据库名称>'")
for table in $list_table
do
mysql -uroot -p<密码> -e "rename table <旧数据库名称>.$table to <新数据库名称>.$table"
done
# mysql 参数：
# -e, --execute=name # 执行mysql的sql语句
# -N, --skip-column-names # 不显示列信息
# -s, --silent # 一行一行输出，中间有tab分隔
DROP DATABASE <数据库名称>;
和创建一样，可以加上if exists
可两篇文章：
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。
其他字段
AUTO_INCREMENT- 默认AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。修改默认值ALTER TABLE 表名 AUTO_INCREMENT=100
CREATE TABLE <表名称>(
<字段名> <类型> [约束条件],
<字段名> <类型> [约束条件],
<字段名> <类型> [约束条件],
);
如：
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P) //PRIMARY KEY约束
)
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY, //PRIMARY KEY约束
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
DESC <表名称>;
-- 或者
SHOW COLUMNS FROM <表名称>;
SHOW TABLES;
SHOW CREATE TABLE <表名称>;
用于在已有的表中添加、删除或修改列。
ALTER TABLE <表名称> ADD <字段名> <类型> [约束条件];
ALTER TABLE <表名称> ADD <字段名> <类型> [约束条件],
ADD <字段名> <类型> [约束条件]，
ADD <字段名> <类型> [约束条件]，
ADD <字段名> <类型> [约束条件]；
FIRST：添加最前AFTER <字段名>：添加指定字段之后 ALTER TABLE data ADD username VARCHAR(20) AFTER id;
-- 在data中往id后面添加一个username字段
-- FIRST的使用方法类似
ALTER TABLE <表名称> DROP <字段名>;
-- 和添加一样可以多行DROP
FIRST和AFTER <字段名>，代表的是修改到哪里。
ALTER TABLE <表名称> MODIFY <字段名> <类型> [约束条件];
-- 和添加一样可以多行MODIFY
ALTER TABLE <表名称> CHANGE <旧字段名> <新字段名> <类型> [约束条件];
-- 和添加一样可以多行CHANGE
DROP TABLE <表名称>
CREATE TABLE <表1名称> (SELECT * FROM <表2名称>);
可以把表2的数据复制到表1中，但不能复制约束性条件。
INSERT INTO <表名称>(行1, 行2...) VALUES(值1, 值2...);
INSERT INTO <表名称>(行1, 行2...) VALUES
(值1, 值2...),
(值1, 值2...),
(值1, 值2...);
(行1, 行2...)这一部分的话，默认一一对应
INSERT INTO <表名称> SET 行1=值1, 行2=值2...;
假如没有筛选的话，就给全部都修改了。可以用WHERE筛选。
-- 修改一个或多个
UPDATE <表名称> SET 行1=值1, ...;
-- 加上WHERE
UPDATE <表名称> SET 行1=值1, ... WHERE ...
假如没有筛选的话，就给全部删除了。相当于清空。
DELETE FROM <表名称>;
清空
TRUNCATE TABLE <表名称>;
先把表删除，然后再建一个。与DELETE FROM相比，TRUNCATE的效率更快，因为DELETE FROM是把记录逐条删除的。
查询执行的顺序
FROM --> WHERE --> SELECT --> GROUP BY --> HAVING --> ORDER BY --> LIMIT
*：SELECT * FROM <表名称>把表中所有的信息都取出来DISTINCT：SELECT DISTINCT <字段名> FROM <表名称>去重AS：SELECT <字段名> AS <别名> FROM <表名称>别名
如：SELECT data+10 AS "数据" FROM <表名称>
| 运算符 | 描述 | 例子 |
|---|---|---|
= |
等于 | SELECT * FROM data WHERE id = 1; |
<> |
不等于。某些 SQL中，可写成 != | 参考=的 |
> |
大于 | 参考=的 |
< |
小于 | 参考=的 |
>= |
大于等于 | 参考=的 |
<= |
小于等于 | 参考=的 |
BETWEEN |
在某个范围内 | SELECT * FROM data WHERE id BETWEEN 2 AND 4; -- 2到4，两边都是闭区间 |
LIKE |
使用通配符搜索 |
%：0 个或多个字符，_：一个字符，[charlist]：字符列中的任何单一字符，[^charlist]或[!charlist]：不在字符列中的任何单一字符；如：WHERE 字段名 LIKE 'a_%_%'以“a”开头且长度至少为3个字符的值 |
IN |
指定针对某个列的多个可能值 | SELECT * FROM data WHERE id IN (1,3,6); |
AND |
||
OR |
||
NOT |
||
IS |
IS NUll或IS NOT NULL
|
ORDER BY
以某一字段名进行排序。
如：SELECT * FROM data ORDER BY name;
可以指定升序(ASC)、降序(DESC)，默认升序。
SELECT * FROM data ORDER BY name DESC;
GROUP BY
SELECT name FROM data GROUP BY name;
SELECT sum(id), name FROM data GROUP BY 2;2代表额是第二个字段name
HAVING 分组后的过滤。 用WHERE的地方可以用HAVING，HAVING可以使用聚合函数，而WHERE不可以使用聚合函数。
SELECT name FROM data GROUP BY name HAVING SUM(id) > 5;
聚合函数
LIMIT与OFFSET
可用于分页，只取一部分。
LIMIT：只有一个数时，表示取几条数据；两个数(a, b)时，表示取[a+1, a+b]这个区间的数据
OFFSET：值跳过多少条数据
SELECT * FROM Customer LIMIT 10; --检索前10行数据，显示1-10条数据
SELECT * FROM Customer LIMIT 1,10; --检索从第2行开始，累加10条id记录，共显示id为2....11
SELECT * FROM Customer LIMIT 10 OFFSET 1; --检索从第2行开始，累加10条id记录，共显示id为2....11
SELECT * FROM Customer LIMIT 5,10; --检索从第6行开始向前加10条数据，共显示id为6,7....15
注意
当数据很大，上百万的时候，使用LIMIT ... OFFSET ..的方式进行分页十分浪费资源且耗时长。最好是结合WHERE使用，如：
-- LIMIT ... OFFSET ..
SELECT * FROM data LIMIT 10 OFFSET 80000001;
-- 10 rows in set (12.80 sec)
-- WHERE
SELECT * FROM data WHERE id > 80000000 LIMIT 10;
-- 10 rows in set (0.01 sec)
REGEXP 使用正则表达进行匹配。查询时，需要搭配WHERE或HAVING使用。
SELECT name FROM data GROUP BY name HAVING name REGEXP 'f$';
SELECT name FROM data WHERE name REGEXP 'f$' GROUP BY name;
两个表之间有交集且要用到两个表的数据时，可以使用内连接查询。
user表：
+----+--------+
| id | name |
+----+--------+
| 1 | 小明 |
| 2 | 小红 |
| 3 | 小林 |
+----+--------+
log表：
+----+-------------+------+
| id | longinCount | uid |
+----+-------------+------+
| 1 | 2 | 3 |
| 2 | 19 | 1 |
| 3 | 5 | 2 |
+----+-------------+------+
-- 使用INNER JOIN ... ON ...
-- 格式：SELECT * FROM <表1名> INNER JOIN <表2名> ON <表1名>.id = <表2名>.uid;
-- 注：表1、表2的顺序可变
SELECT * FROM user INNER JOIN log ON user.id=log.uid;
-- 也可以直接查询
-- 格式：SELECT * FROM <表1名>, <表2名> WHERE <表1名>.id = <表2名>.uid;
SELECT * FROM user, log WHERE user.id=log.uid;
/*
+----+--------+----+-------------+------+
| id | name | id | longinCount | uid |
+----+--------+----+-------------+------+
| 3 | 小林 | 1 | 2 | 3 |
| 1 | 小明 | 2 | 19 | 1 |
| 2 | 小红 | 3 | 5 | 2 |
+----+--------+----+-------------+------+
*/
-- 排序并使用别名
SELECT user.id AS UID, user.name AS Name, log.longinCount FROM user, log WHERE user.id=log.uid ORDER BY user.id;
/*
+-----+--------+-------------+
| UID | Name | longinCount |
+-----+--------+-------------+
| 1 | 小明 | 19 |
| 2 | 小红 | 5 |
| 3 | 小林 | 2 |
+-----+--------+-------------+
*/
LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
name1:
+----+--------+------------+
| id | name | loginCount |
+----+--------+------------+
| 1 | 小明 | 23 |
| 2 | 小红 | 12 |
| 3 | 小刚 | 53 |
| 4 | 小吕 | 123 |
+----+--------+------------+
name2:
+----+--------+-----------+
| id | name | comment |
+----+--------+-----------+
| 1 | 小刚 | 直男 |
| 2 | 小红 | 白富美 |
| 3 | 小明 | 学霸 |
+----+--------+-----------+
用法：
/*
SELECT * FROM <表1名>
LEFT JOIN <表2名>
ON <表1名>.<某字段> = <表2名>.<某字段>;
*/
-- 例子
SELECT * FROM name1 LEFT JOIN name2 ON name1.name=name2.name;
/*
+----+--------+------------+------+--------+-----------+
| id | name | loginCount | id | name | comment |
+----+--------+------------+------+--------+-----------+
| 3 | 小刚 | 53 | 1 | 小刚 | 直男 |
| 2 | 小红 | 12 | 2 | 小红 | 白富美 |
| 1 | 小明 | 23 | 3 | 小明 | 学霸 |
| 4 | 小吕 | 123 | NULL | NULL | NULL |
+----+--------+------------+------+--------+-----------+
*/
-- 没有匹配到，就为NULL
-- 美化一下
SELECT name1.id, name1.name, name2.comment FROM name1 LEFT JOiN name2 ON name1.name=name2.name ORDER BY name1.id;
/*
+----+--------+-----------+
| id | name | comment |
+----+--------+-----------+
| 1 | 小明 | 学霸 |
| 2 | 小红 | 白富美 |
| 3 | 小刚 | 直男 |
| 4 | 小吕 | NULL |
+----+--------+-----------+
*/
RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。把LEFT JOIN的表1、表2调换顺序，就是REGHT JOIN。
SELECT * FROM name2 right JOIN name1 ON name1.name=name2.name;
/*
+------+--------+-----------+----+--------+------------+
| id | name | comment | id | name | loginCount |
+------+--------+-----------+----+--------+------------+
| 1 | 小刚 | 直男 | 3 | 小刚 | 53 |
| 2 | 小红 | 白富美 | 2 | 小红 | 12 |
| 3 | 小明 | 学霸 | 1 | 小明 | 23 |
| NULL | NULL | NULL | 4 | 小吕 | 123 |
+------+--------+-----------+----+--------+------------+
*/
FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.相当于结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
但MySQL中不支持 FULL OUTER JOIN。
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
即SELECT嵌套。
SELECT * FROM data WHERE name IN (SELECT name FROM user);
SELECT * FROM data WHERE EXISTS (SELECT name FROM user);
索引的本质是一种排好序的数据结构。利用索引可以提高查询速度。
常见的索引有：
主键
CREATE TABLE test(id TINYINT PRIMARY KEY, name VARCHAR(20));
-- 或
CREATE TABLE test(id TINYINT, name VARCHAR(20), PRIMARY KEY (id));
/*
mysql> SHOW CREATE TABLE test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` tinyint(4) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
*/
非主键 普通索引
-- 格式：
-- CREATE TABLE <表名> ( <字段> <类型>等 ... , INDEX|KEY [索引名] (<字段名> [长度] [ASC|GESC]));
-- | 表示或
CREATE TABLE test (name VARCHAR(20), INDEX n (name));
/*
mysql> SHOW CREATE TABLE test;
+-------+------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`name` varchar(20) DEFAULT NULL,
KEY `n` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
*/
UNIQUE、FULLTEXT、SPATIAL索引 这三个分别对应唯一索引、全文索引、多列索引。
-- CREATE TABLE <表名> ( <字段> <类型>等 ... ,
-- UNIQUE | FULLTEXT | SPATIAL [ INDEX | KEY] [索引名] [索引类型] (<字段名>,…)
CREATE TABLE test(name VARCHAR(20), UNIQUE INDEX(name));
/*
mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`name` varchar(20) DEFAULT NULL,
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
*/
/*
原表
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | tinyint(4) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
*/
主键
ALTER TABLE test ADD PRIMARY KEY (id);
/*
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | tinyint(4) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
*/
非主键索引
普通索引
-- 格式和上面的一样
ALTER TABLE test ADD INDEX(name);
UNIQUE、FULLTEXT、SPATIAL索引
-- 格式和上面的一样
ALTER TABLE test ADD UNIQUE INDEX(name);
-- 格式：
-- DROP INDEX <索引名> ON <表名>;
DROP INDEX name ON test;
-- 格式：
-- ALTER TABLE <表名> DROP PRIMARY KEY;
ALTER TABLE test DROP PRIMARY KEY;
MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 外键的使用条件：
外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。
-- 表1字表，表2主表
CREATE TABLE <表1名称>
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id), -- 主键也可在字段后面创建
FOREIGN KEY (P_Id) REFERENCES <表2名称>(<表2的ID>)
)；
-- user表
CREATE TABLE user(
id TINYINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
-- data表
CREATE TABLE data(
id TINYINT PRIMARY KEY AUTO_INCREMENT,
loginCount TINYINT,
uid TINYINT,
FOREIGN KEY (uid) REFERENCES user(id)
);
对已有的两个表增加外键 比如：主表为A，子表为B，外键为aid，外键约束名字为a_fk_b
ALTER TABLE B ADD aid TINYINT;
-- 注意对应上
ALTER TABLE B ADD CONSTRAINT a_fk_b FOREIGN KEY(aid) REFERENCES A(id);
删除外键约束条件
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束的名字>;
ALTER TABLE data DROP FOREIGN KEY data_ibfk_1;
外键约束的名字需要通过SHOW CREATE TABLE命令查看
外键
先删除外键约束条件，再删外键字段
第一步在上面
第二步使用命令：ALTER TABLE <表名> DROP <外键字段名>;
删除记录
假如删除记录报错：[Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...)
这是因为MySQL中设置了foreign key关联，造成无法更新或删除数据。可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况。
第一步：禁用外键约束，我们可以使用：SETFOREIGN_KEY_CHECKS=0;
第二步：删除数据
第三步：启动外键约束，我们可以使用：SETFOREIGN_KEY_CHECKS=1;
查看当前FOREIGN_KEY_CHECKS的值，可用如下命令：SELECT @@FOREIGN_KEY_CHECKS;
练习题来源于：sql语句练习50题(Mysql版)
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
