数据库

创建数据库

1
CREATE DATABASE 数据库名;

删除数据库

1
DROP DATABASE School_database;

选择数据库

1
USE 数据库名;

数据类型

数值类型

类型 大小 用途
TINYINT 1 Bytes 小整数
SMALLINT 2 Bytes 小整数
INT 4 Bytes 大整数
FLOAT 4 Bytes 单精度浮点数
DOUBLE 8 Bytes 双精度浮点数

日期时间类型

类型 大小 格式 用途
DATE 3 Bytes YYYY-MM-DD 日期值
TIME 3 Bytes HH:MM:SS 时间值或持续时间
YEAR 1 Bytes YYYY 年份值
DATETIME 8 Bytes YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 Bytes YYYYMMDD HHMMSS 混合日期和时间值，时间戳

字符串类型

类型 大小 用途
CHAR 0-255 Bytes 定长字符串
VARCHAR 0-65535 Bytes 变长字符串

数据表

创建数据表

1
2
3
4
5
6
7
8
9
CREATE TABLE Students(
    stu_id INT NOT NULL AUTO_INCREMENT,
    stu_name VARCHAR(20) NOT NULL,
    stu_sex VARCHAR(2),
    stu_high INT,
    stu_weight DOUBLE,
    stu_birth DATE,
    PRIMARY KEY(stu_id)
)CHARSET=utf8;
  • TEMPORARY用来设置临时表，临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放空间。
  • NOT NULL表示该字段值不可以为NULL。
  • AUTO_INCREMENT定位该列的自增属性，一般用于主键，数值会自动增加1。
  • PRIMARY KEY用来定义列为主键。
  • CHARSET设置编码。

删除数据表

1
DROP TABLE Students；

修改数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--增加字段
ALTER TABLE employee ADD stu_address VARCHAR(10);
--删除字段
ALTER TABLE employee DROP stu_address;
--修改字段类型
ALTER TABLE employee MODIFY stu_address VARCHAR(5);
--修改字段名和类型
ALTER TABLE employee CHANGE	stu_address stu_addr VARCHAR(5);
--修改字段默认值
ALTER TABLE employee ALTER stu_addr SET DEFAULT "here";
--删除字段默认值
ALTER TABLE employee ALTER stu_addr DROP DEFAULT;
--修改数据表名
ALTER TABLE employee RENAME employ;

查看数据表

1
SHOW COLUMNS FROM employee;

复制数据表

1
2
3
4
5
6
7
8
9
10
--查询创建信息
SHOW CREATE employee；
--复制表内容
INSERT INTO employee_copy ( stu_id, stu_name, stu_salary, stu_addr) SELECT
stu_id,
stu_name,
stu_salary,
stu_addr 
FROM
	employee;

  • 可以使用SHOW CREATE语句获取创建该表的创建命令，修改表名并运行即可复制一个新表。

  • 使用INSERT INTO … SELECT可以复制表的内容。

插入数据

1
2
3
4
INSERT INTO Students
(stu_name,stu_sex,stu_high,stu_weight,stu_birth)
values
('fengbin', 'm', 175, 60, '1997-10-25');

查询数据

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT
	*
FROM
	students
WHERE
	stu_high <= 160 AND
	stu_sex = 'F' AND
	stu_name LIKE 'Han%'
ORDER BY
	stu_high, stu_weight DESC
LIMIT 5, 1;

数据排序

  • ORDER BY 为按字段排序，默认为ASC升序，DESC为降序，可使用多字段排序，规则为先按第一个字段排序，再按第二个字段排序。

数据截取

  • LIMIT A 为截取前A条记录，LIMIT B,A 为偏移B条记录取A条记录。

模糊匹配

  • LIKE子句用来查找满足对应规则的记录，其中%代表任意字符。
1
2
3
4
5
6
7
8
9
10
11
SELECT
	stu_name
FROM
	students
UNION
SELECT
	stu_name
FROM
	employee
ORDER BY
	stu_name;

数据联合

  • UNION 语句将两个查询结果进行联合并去重，若改为UNION ALL则不去重。
1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
    COALESCE ( stu_sex, 'total' ) AS stu_sex,
	COALESCE ( stu_high, 'total' ) AS stu_high,
	COUNT(*),
	MAX( stu_high ) AS max_high 
FROM
	STUDENTS 
WHERE
	stu_name LIKE '%Sum%' 
GROUP BY
	stu_sex,
	stu_high WITH ROLLUP;

数据分组

  • GROUP BY对一个或多个列进行分组，在分组的列上可以使用SUM，AVG，COUNT等函数对值进行操作。
  • 在GROUP BY最后加上WITH ROLLUP，可以实现在分组的基础上再进行相同的统计（SUM，AVG，COUNT……）。
  • 可以使用COALESCE语句给在统计的NULL设置一个别称。

更新数据

1
2
3
4
5
6
UPDATE
	Students
SET
	stu_sex = 'M'
WHERE
	stu_id <= 10;
  • 将满足WHERE条件的数据按SET更新数据。

删除数据

1
2
3
4
5
DELETE
FROM
	Students
WHERE
	stu_id = 1000;
  • 将满足WHERE条件的数据删除。

连接数据

内连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
	a.stu_id,
	a.stu_name,
	a.stu_sex,
	b.stu_salary 
FROM
	students a
	INNER JOIN employee b ON a.stu_id = b.stu_id;
	
--以上SQL语句等价为
SELECT
	a.stu_id,
	a.stu_name,
	a.stu_sex,
	b.stu_salary 
FROM
	students a,
	employee b 
WHERE
	a.stu_id = b.stu_id;
  • INNER JOIN 会读取两个表的交集数据。

左连接

1
2
3
4
5
6
7
8
SELECT
	a.stu_id,
	a.stu_name,
	a.stu_sex,
	b.stu_salary 
FROM
	students a
	LEFT JOIN employee b ON a.stu_id = b.stu_id;
  • LEFT JOIN会读取A表的全部数据，即使B表没有对应数据。

右连接

1
2
3
4
5
6
7
8
SELECT
	a.stu_id,
	a.stu_name,
	a.stu_sex,
	b.stu_salary 
FROM
	students a
	RIGHT JOIN employee b ON a.stu_id = b.stu_id;
  • RIGHT JOIN会读取B表的全部数据，即使A表没有对应数据。

NULL值处理

1
2
3
4
5
6
SELECT
	*
FROM
	employee
WHERE
	stu_salary IS NOT NULL;
  • 使用IS (NOT) NULL来判断值是否为NULL，不能使用=和!=来判断。
1

  • IFNULL(expr1, expr2)，如何expr1不为NULL，则返回expr1的值，如果expr1为NULL,则返回expr2的值。

正则匹配

模式 描述
^ 匹配输入字符串的起始位置。
$ 匹配输入字符串的结束位置。
. 匹配除’\n’外的任意单个字符。
[…] 字符集合，匹配其中的任意一个字符。’[xy]’可以匹配x或者y。
[^…] 负值字符匹配，匹配其中不包含的任意字符。
a|b|c 匹配a或b或c。’z|food’可以匹配z或food。’(z|f)ood’可以匹配zood或者food
* 匹配前面的子式零次或多次。’zo*’可以匹配’z’或’zo’或’zoo’等。 *等价于{0，}
+ 匹配前面的子式一次或多次。’zo+’可以匹配’zo’或’zoo’等。 +等价于{1，}
{n} 匹配前面子式n(n≥0)次。
{n,m} 匹配前面子式最少n(n≥0)次，最多m(m≥0)次。 
1
2
3
4
5
6
7
SELECT
	*
FROM
	employee
WHERE
	stu_name REGEXP 'Ye{1,}';
  • REGEXP用于正则匹配，匹配stu_name中有’Ye’或’Yee‘或’Yeee’等的数据。

事务

事务特性

  • 原子性：一个事务的所有操作，要么全部完成要么全部不完成。事务中出现差错会回滚到事务开始前的状态。
  • 一致性：事务开始前和结束后，数据库完整性没有被破坏。
  • 隔离性：数据库允许多个并发事务同时对数据进行读写和修改。隔离性要防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  • 持久性：事务处理结束后，对数据的修改是永久的，即使系统故障也不会丢失。

事务控制语句

  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认

索引

查询索引

1
SHOW INDEX FROM employee;

普通索引

1
2
3
4
5
6
--添加索引
CREATE INDEX index_name ON employee(stu_name);
ALTER TABLE employee ADD INDEX index_name(stu_name);
--删除索引
DROP INDEX index_name ON employee;

唯一索引

索引值必须唯一，可以为空值。如果是组合索引，组合的列值必须唯一。

1
2
3
4
5
--添加索引
CREATE INDEX index_name ON employee(stu_name);
ALTER TABLE employee ADD INDEX index_name(stu_name);
--删除索引
DROP INDEX index_name ON employee;

重复数据

  • 可以设置字段为PRIMARY KEY(主键)或者UNIQUE索引来保证数据的唯一性。
  • 再插入时使用INSERT IGNORE INTO可以跳过重复数据的插入。
  • 使用DISTINCT关键字来过滤重复数据。

统计重复数据

1
2
3
4
5
6
7
8
9
10
11
SELECT
	COUNT(*) AS stu_num,
	stu_high 
FROM
	students 
GROUP BY
	stu_high 
HAVING
	stu_num > 1 
ORDER BY
	stu_high;
  • 统计stu_high重复数大于1的数据。

删除重复数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--复制一个无重复数据的表
CREATE TABLE temp SELECT
stu_id,
stu_name,
stu_salary 
FROM
	employee 
GROUP BY
	stu_id,
	stu_name,
	stu_salary;
--删除原表
DROP TABLE employee;
--修改新表的名字
ALTER TABLE temp RENAME employee;