MySQL数据库

1.语法

1.1 查看数据库

show databases;
show tables;
DESCRIBE table；
SELECT * FROM employee WHERE ___;

1.2 数据库相关语句

新建数据库
CREATE DATABASE 数据库名;
删除数据库
DROP DATABASE 数据库名

1.3 表相关语句

1、重命名表

RENAME TABLE 原名 TO 新名字;

ALTER TABLE 原名 RENAME 新名;

ALTER TABLE 原名 RENAME TO 新名;

2、删除一张表

DROP TABLE 表名字;

4、对一列的修改(即对表结构的修改)

增加一列（默认添加到最后，关键字after可指定位置，first可指定加入排头）
ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
或： ALTER TABLE 表名字 ADD 列名字 数据类型 约束;

mysql> alter table employee add height int(4) default 170;

mysql> alter table employee add weight int(4) default 120 after age;

mysql> ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;

5、删除一列

ALTER TABLE 表名字 DROP COLUMN 列名字;

或： ALTER TABLE 表名字 DROP 列名字;

6、重命名一列

ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;

7、改变数据类型

ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

8、修改表中某个值

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

9、删除一行记录

delete from 表名 where 条件；

1.3.2 选择语句

条件：【=,<,>,>=,<=】【OR或者AND】【IN 和 NOT IN】【通配符】【对结果排序ORDER BY】

1、基础elect and or 和 between

SELECT name,age,phone FROM employee WHERE name='Mary';

筛选出 age 小于 25，或 age 大于 30

SELECT name,age FROM employee WHERE age<25 OR age>30;

如果需要包含25和30这两个数字的话，可以替换为 age BETWEEN 25 AND 30 ：

mysql> select name,age from employee where age between 25 and 30;

2、限定条件 in 和not in

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

**3、通配符【重点】_ 和 % **

关键字 LIKE 在SQL语句中和通配符一起使用，通配符代表未知字符。SQL中的通配符是 _ 和 % 。
其中 _ 代表一个未指定字符，% 代表不定个未指定字符。

SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';

另一种情况，比如只记名字的首字母，又不知道名字长度，则用 % 通配符代替不定个字符：

SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

**4、对结果排序 关键字 ORDER BY **

默认情况下，ORDER BY的结果是升序排列，而使用关键词ASC和DESC可指定升序或降序排序。

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

5、SQL 内置函数和计算

共5个：
函数名： COUNT SUM AVG MAX MIN
作用： 计数 求和 求平均值 最大值 最小值

使用AS关键词可以给值重命名，比如最大值被命名为了max_salary：

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
+------------+-------------+
| max_salary | min(salary) |
+------------+-------------+
|       3600 |        2500 |
+------------+-------------+

6、子查询【重点】 需要显示的内容在一个表中

需要多个表中查询内容
想要知道名为 “Tom” 的员工所在部门做了几个工程。

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project    -- count 计数，几个工程的count
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');
+--------+---------------+
| of_dpt | count_project |
+--------+---------------+
| dpt4   |             2 |
+--------+---------------+

7、连接查询【重点】 join关键字 需要显示的内容在多个表中

各员工所在部门的人数，其中员工的 id 和 name 来自 employee 表，people_num 来自 department 表：

mysql> select id,name,people_num
    -> from employee,department
    -> where employee.in_dpt=department.dpt_name
    -> order by id;

+----+------+------------+
| id | name | people_num |
+----+------+------------+
|  1 | Tom  |         15 |
|  2 | Jack |         12 |
|  3 | Rose |         10 |
|  4 | Jim  |         11 |
|  5 | Mary |         12 |
|  6 | Alex |         11 |
|  7 | Ken  |         11 |
|  8 | Rick |         10 |
|  9 | Joe  |         12 |
| 10 | Mike |         15 |
| 11 | Jobs |         12 |
| 12 | Tony |         10 |
+----+------+------------+

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;
条件：
SELECT name,age,phone FROM employee WHERE name='Mary' OR age>25;
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

1.3 数据类型

整数：
| 数据类型 | 大小(字节) | 用途 | 格式 |
| ------------- |:-------------?: -----|: -----|
| INT | 4 | 整数 | |
| FLOAT | 4 | 单精度浮点数 |
| DOUBLE | 8 | 双精度浮点数 |
选择：
| 数据类型 | 大小(字节) | 用途 | 格式 |
| ------------- |:-------------?: -----|: -----|
| ENUM | | 单选 | ENUM(‘a’,‘b’,‘c’) |
| SET | | 多选 | SET(‘1’,‘2’,‘3’) |
时间：
| 数据类型 | 大小(字节) | 用途 | 格式 |
| ------------- |:-------------?: -----|: -----|
| DATE | 3 | 日期 | YYYY-MM-DD |
| TIME | 3 | 时间 | HH:MM:SS |
| YEAR | 1 | 年份 | YYYY |
字符串：
| 数据类型 | 大小(字节) | 用途 | 格式 |
| ------------- |:-------------?: -----|: -----|
| CHAR | 0~255 | 定长字符串 |
| VARCHAR | 0~255 | 变长字符串 |
| TEXT | 0~65535 | 长文本 |

CHAR 和 VARCHAR 的区别:
存储字符串 “abc"，
CHAR(10)，表示存储的字符将占 10 个字节(包括 7 个空字符)
VARCHAR(12) 则只占用4个字节的长度，增加一个额外字节来存储字符串本身的长度，12 只是最大值

2. SQL约束

2.1 约束分类

| 约束类型： | 主键 | 默认值 | 唯一 | 外键 | 非空 |
| ------------- |:-------------|:-----|
| 关键字： | PRIMARY KEY | DEFAULT | UNIQUE | FOREIGN KEY| NOT NULL |

1.1 主键 (PRIMARY KEY)
是用于约束表中的一行，作为这一行的唯一标识符，在一张表中通过主键就能准确定位到一行。主键不能有重复且不能为空。

//定义主键的方法
id INT(20) PRIMARY KEY,
CONSTRAINT 主键名 PRIMARY KEY (主键列名）,
//复合主键
CONSTRAINT 主键名 PRIMARY KEY (主键列名1，主键列名2）,

1.2 默认值约束(DEFAULT )

1.3 唯一约束（UNIQUE）
它规定一张表中指定的一列的值必须不能有重复值

create table xxx(
	phone int(20),
	UNIQUE(phone)
);

1.4 外键约束(FOREIGN KEY)
一个表可以有多个外键，每个外键必须 REFERENCES (参考) 另一个表的【主键】，被外键约束的列，取值必须在它参考的列中有对应值。

create table xxx(
	phone int(20),
	CONSTRAINT 外键名字 FOREIGN KEY(表中外键列名 比如phone) REFERENCES 另一张表名（参考列） 
);

1.5 非空约束（NOT NULL）

3. 索引和视图

1、索引【重点】

索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录。这样做会消耗大量数据库系统时间，并造成大量磁盘 I/O 操作。

而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度。

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);

CREATE INDEX 索引名 ON 表名字 (列名);

ALTER TABLE employee ADD INDEX idx_id (id);  #在employee表的id列上建立名为idx_id的索引

CREATE INDEX idx_name ON employee (name);   #在employee表的name列上建立名为idx_name的索引

查询语句

SHOW INDEX FROM 表名字

2、视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。

1.数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；
2.列表项目使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；
3.视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；
4.在使用视图的时候，可以把它当作一张表。

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

4.操作详解

排序

关键字：order by
使用BINARY强制执行区分大小写的排序功能

mysql> SELECT name, birth FROM pet ORDER BY BINARY birth;

默认排序是升序，也就是最小的值排在第一。要想以降序排序，在你正在排序的列名旁边增加DESC（降序 ）关键字：

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

【重点】按升序对动物的种类进行排序，然后按降序根据生日对各动物种类进行排序

mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;

模式匹配 (关键字 like % _ […] )

SQL模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)。
SQL模式时，不能使用=或!=；而应使用LIKE或NOT LIKE比较操作符。

# 以“b”开头的名字的动物信息
mysql> SELECT * FROM pet WHERE name LIKE 'b%';

# 要想找出以“fy”结尾的名字：
mysql> SELECT * FROM pet WHERE name LIKE '%fy';

# 要想找出包含“w”的名字：
mysql> SELECT * FROM pet WHERE name LIKE '%w%';

# 找出正好包含5个字符的名字
mysql> SELECT * FROM pet WHERE name LIKE '_____';

1、‘.’匹配任何单个的字符。
2、“[…]”匹配在方括号内的任何字符。
3、“ ”匹配零个或多个在它前面的字符。例如，“x”匹配任何数量的“x”字符，“[0-9]”匹配任何数量的数字，而“.”匹配任何数量的任何字符。

如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。
模式开始处使用“^”或在模式的结尾用“$”。

# 出以“b”开头的名字，使用“^”匹配名字的开始：
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
# 如果没有^ 只有‘b’，select结果是name中任意有b值的项

# 如果想强制使REGEXP比较区分大小写，使用BINARY关键字该查询只匹配名称首字母的小写‘b’。
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

# 找出以“fy”结尾的名字
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

# 为了找出包含正好5个字符的名字，“^”和“$”匹配名字的开始和结尾，和5个“.”
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
# 也可以使用“{n}”重复n次操作符,
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

计算行数 查询出某个类型的数据在表中出现的频数是多少?

SELECT in_dpt,COUNT(in_dpt) AS COUNT FROM employee GROUP BY in_dpt;

SELECT age,COUNT(age),NAME FROM employee GROUP BY age;