MySQL学习笔记

                                     MySQL学习笔记

1.开发环境：Ubuntu18.04
MySQL安装：
#安装 MySQL 服务端、核心程序
sudo apt-get install mysql-server

#安装 MySQL 客户端
sudo apt-get install mysql-client  

命令验证是否安装并启动成功
sudo netstat -tap | grep mysql 

根据自己的需求，用 gedit 修改 MySQL 的配置文件（my.cnf）
sudo gedit /etc/mysql/my.cnf 

基本指令：
# 启动 MySQL 服务
sudo service mysql start             
# 使用 root 用户登录
mysql -u root;
#mysql -u root -p;//有密码就这个；

                          创建数据库插入数据

system clear即可完成清屏
show databases;查看有哪些数据库（注意不要漏掉分号 ;）：
use information_schema 连接数据库
show tables; 查看数据库中有哪些表（注意不要漏掉“;”）
quit 或者 exit 退出 MySQL

CREATE DATABASE jt_Database;建立数据库
use jt_Database;链接数据库

CREATE TABLE employee (
id int(10),
name char(20),
phone int(12)
);建立表

建立表的格式：
CREATE TABLE 表的名字
(
列名a 数据类型(数据长度),
列名b 数据类型(数据长度)，
列名c 数据类型(数据长度)
);

数据类型	大小(字节)	用途	格式
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	长文本数据	

通过 INSERT 语句向表中插入数据，语句格式为：
INSERT INTO 表的名字(列名a,列名b,列名c) VALUES(值1,值2,值3);
尝试向 employee 中加入 Tom、Jack 和 Rose：
INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);
INSERT INTO employee VALUES(02,'Jack',119119119);
INSERT INTO employee(id,name) VALUES(03,'Rose');
SELECT * FROM employee; 查看 employee 表


                             SQL约束

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

主键定义：
CONSTRAINT 自定义主键名 PRIMARY KEY(主键1,主键2)；

默认值约束：

DEFAULT 约束只会在使用 INSERT 语句时体现出来
如果被 DEFAULT 约束的位置没有值，那么这个位置将会被 DEFAULT 的值填充
mysql> CREATE TABLE TEST(
    -> NUM INT(10) DEFAULT 144
    -> );

唯一约束:
UNIQUE(NAME)//唯一约束；
这一列只能有一个；
mysql> CREATE TABLE ONLY(
    -> ID INT(10),
    -> NAME CHAR(20),
    -> UNIQUE(NAME)//唯一约束；
    -> );

Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO ONLY(ID,NAME) VALUES(01,'AAA');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO ONLY(ID,NAME) VALUES(02,'AAA');
ERROR 1062 (23000): Duplicate entry 'AAA' for key 'NAME'


外键约束：
CONSTRAINT 外键名字（自定义） FOREIGN KEY(外键） REFERENCES 表名称(内部名称)

非空约束：
非空约束 (NOT NULL),听名字就能理解，被非空约束的列，在插入值时必须非空。

mysql> CREATE TABLE NOTN(
    -> ID INT(10),
    -> NUM INT(10) NOT NULL
    -> );

mysql> INSERT INTO NOTN(ID) VALUES(01);
ERROR 1364 (HY000): Field 'NUM' doesn't have a default value
mysql> INSERT INTO NOTN(ID,NUM) VALUES(01,02);
Query OK, 1 row affected (0.01 sec)

                        SELECT语句
SELECT 语句的基本格式为：
SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;
如果要查询表的所有内容，则把 要查询的列名 用一个星号 * 号表示.


select phone,id from employee where id > 3;
+-------+------+
| phone | id   |
+-------+------+
|  NULL |    4 |
|  NULL |    5 |
+-------+------+

mysql> select id,name,phone from employee where id = 3;
+------+------+-------+
| id   | name | phone |
+------+------+-------+
|    3 | TT   |  NULL |
+------+------+-------+

AND && OR

select id,name,phone from employee where id > 1 and id <3;


select id,name,phone from employee where id > 4 or id <3;

IN && NOT IN

//列出employee中id 是4的行
mysql> select id,name,phone from employee where id in (4);
+------+------+-------+
| id   | name | phone |
+------+------+-------+
|    4 | 4T   |  NULL |
+------+------+-------+
1 row in set (0.00 sec)

//列出employee中id 不是4和5的行
mysql> select id,name,phone from employee where id not in (4,5);
+------+------+-----------+
| id   | name | phone     |
+------+------+-----------+
|    1 | Tom  | 110110110 |
|    2 | Eric |      NULL |
|    3 | TT   |      NULL |
+------+------+-----------+
3 rows in set (0.00 sec)

通配符
//_代表一个字符
mysql> select id,name,phone from employee where name LIKE 'T__';
+------+------+-----------+
| id   | name | phone     |
+------+------+-----------+
|    1 | Tom  | 110110110 |
+------+------+-----------+
1 row in set (0.01 sec)

//%代表T开头的所有行
mysql> select id,name,phone from employee where name LIKE 'T%';
+------+------+-----------+
| id   | name | phone     |
+------+------+-----------+
|    1 | Tom  | 110110110 |
|    3 | TT   |      NULL |
+------+------+-----------+
2 rows in set (0.00 sec)

输出结果排序：
用到 ORDER BY 排序关键词,
默认情况下，ORDER BY 的结果是升序排列,
关键词 ASC 和 DESC 可指定升序或降序排序.

mysql> select id,name,phone from employee order by id ASC;
+------+------+-----------+
| id   | name | phone     |
+------+------+-----------+
|    1 | Tom  | 110110110 |
|    2 | Eric |      NULL |
|    3 | TT   |      NULL |
|    4 | 4T   |      NULL |
|    5 | 5T   |      NULL |
+------+------+-----------+
5 rows in set (0.01 sec)

mysql> select id,name,phone from employee order by id desc;
+------+------+-----------+
| id   | name | phone     |
+------+------+-----------+
|    5 | 5T   |      NULL |
|    4 | 4T   |      NULL |
|    3 | TT   |      NULL |
|    2 | Eric |      NULL |
|    1 | Tom  | 110110110 |
+------+------+-----------+
5 rows in set (0.01 sec)

SQL 内置函数和计算
其中 COUNT 函数可用于任何数据类型(因为它只是计数)，
而 SUM 、AVG 函数都只能对数字类数据类型做计算，
MAX 和 MIN 可用于数值、字符串或是日期时间数据类型。

函数名：	COUNT	SUM	   AVG	     MAX	 MIN
作用：	计数	    求和	  求平均值	最大值	最小值
as 起到重命名的作用：

mysql> select max(id) as MAXID,min(id) as MINID,count(id) as COUNT from employee;
+-------+-------+-------+
| MAXID | MINID | COUNT |
+-------+-------+-------+
|     5 |     1 |     5 |
+-------+-------+-------+
1 row in set (0.00 sec)

子查询：
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project GROUP BY of_dpt
HAVING of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');
上面代码包含两个 SELECT 语句，第二个 SELECT 语句将返回一个集合的数据形式，然后被第一个 SELECT 语句用 in 进行判断。

HAVING 关键字可以的作用和 WHERE 是一样的，都是说明接下来要进行条件筛选操作。

区别在于 HAVING 用于对分组后的数据进行筛选

连接查询：
在处理多个表时，子查询只有在结果来自一个表时才有用。但如果需要显示两个表或多个表中的数据，这时就必须使用连接 (join) 操作。 连接的基本思想是把两个或多个表当作一个新的表来操作，如下：
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;

                                    数据库和表的修改和删除
//删除数据库xxx
DROP DATABASE xxx;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jt_Database        |
| library            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> DROP DATABASE mysql;
Query OK, 31 rows affected, 2 warnings (0.36 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jt_Database        |
| library            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

重命名一张表
ALTER TABLE 原名 RENAME 新名;
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_jt_Database |
+-----------------------+
| EMPLY                 |
| EMPLY2                |
| NOTN                  |
| ONLY                  |
| PROJECT               |
| SHIT                  |
| employee              |
| row                   |
| workers               |
+-----------------------+
9 rows in set (0.00 sec)

mysql> ALTER TABLE row RENAME HELLO;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_jt_Database |
+-----------------------+
| EMPLY                 |
| EMPLY2                |
| HELLO                 |
| NOTN                  |
| ONLY                  |
| PROJECT               |
| SHIT                  |
| employee              |
| workers               |
+-----------------------+
9 rows in set (0.00 sec)

删除一张表
DROP TABLE 表名字;

mysql> DROP TABLE HELLO;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_jt_Database |
+-----------------------+
| EMPLY                 |
| EMPLY2                |
| NOTN                  |
| ONLY                  |
| PROJECT               |
| SHIT                  |
| employee              |
| workers               |
+-----------------------+
8 rows in set (0.00 sec)

增加一列
ALTER TABLE employee ADD height INT(4) DEFAULT 170;

mysql> SELECT * FROM employee;
+------+------+-----------+--------+
| id   | name | phone     | height |
+------+------+-----------+--------+
|    1 | Tom  | 110110110 |   NULL |
|    2 | Eric |      NULL |   NULL |
|    3 | TT   |      NULL |   NULL |
|    4 | 4T   |      NULL |   NULL |
|    5 | 5T   |      NULL |   NULL |
+------+------+-----------+--------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE employee ADD age INT(4) DEFAULT 0;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employee;
+------+------+-----------+--------+------+
| id   | name | phone     | height | age  |
+------+------+-----------+--------+------+
|    1 | Tom  | 110110110 |   NULL |    0 |
|    2 | Eric |      NULL |   NULL |    0 |
|    3 | TT   |      NULL |   NULL |    0 |
|    4 | 4T   |      NULL |   NULL |    0 |
|    5 | 5T   |      NULL |   NULL |    0 |
+------+------+-----------+--------+------+

比如我们新增一列 weight(体重) 放置在 age(年龄) 的后面：
ALTER TABLE employee ADD weight INT(4) DEFAULT 120 AFTER age;
mysql> ALTER TABLE employee ADD weight INT(10) DEFAULT 120 AFTER phone;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employee;
+------+------+-----------+--------+--------+------+
| id   | name | phone     | weight | height | age  |
+------+------+-----------+--------+--------+------+
|    1 | Tom  | 110110110 |    120 |   NULL |    0 |
|    2 | Eric |      NULL |    120 |   NULL |    0 |
|    3 | TT   |      NULL |    120 |   NULL |    0 |
|    4 | 4T   |      NULL |    120 |   NULL |    0 |
|    5 | 5T   |      NULL |    120 |   NULL |    0 |
+------+------+-----------+--------+--------+------+
5 rows in set (0.00 sec)

放在第一列的位置
ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;

mysql> ALTER TABLE employee ADD num INT(4) DEFAULT 1 FIRST;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employee;
+------+------+------+-----------+--------+--------+------+
| num  | id   | name | phone     | weight | height | age  |
+------+------+------+-----------+--------+--------+------+
|    1 |    1 | Tom  | 110110110 |    120 |   NULL |    0 |
|    1 |    2 | Eric |      NULL |    120 |   NULL |    0 |
|    1 |    3 | TT   |      NULL |    120 |   NULL |    0 |
|    1 |    4 | 4T   |      NULL |    120 |   NULL |    0 |
|    1 |    5 | 5T   |      NULL |    120 |   NULL |    0 |
+------+------+------+-----------+--------+--------+------+
5 rows in set (0.00 sec)

删除一列
ALTER TABLE 表名字 DROP 列名字;

mysql> ALTER TABLE employee DROP num;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employee;
+------+------+-----------+--------+--------+------+
| id   | name | phone     | weight | height | age  |
+------+------+-----------+--------+--------+------+
|    1 | Tom  | 110110110 |    120 |   NULL |    0 |
|    2 | Eric |      NULL |    120 |   NULL |    0 |
|    3 | TT   |      NULL |    120 |   NULL |    0 |
|    4 | 4T   |      NULL |    120 |   NULL |    0 |
|    5 | 5T   |      NULL |    120 |   NULL |    0 |
+------+------+-----------+--------+--------+------+
5 rows in set (0.00 sec)

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

mysql> ALTER TABLE employee CHANGE height high INT(4) DEFAULT 170;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employee;
+------+------+-----------+--------+------+------+
| id   | name | phone     | weight | high | age  |
+------+------+-----------+--------+------+------+
|    1 | Tom  | 110110110 |    120 | NULL |    0 |
|    2 | Eric |      NULL |    120 | NULL |    0 |
|    3 | TT   |      NULL |    120 | NULL |    0 |
|    4 | 4T   |      NULL |    120 | NULL |    0 |
|    5 | 5T   |      NULL |    120 | NULL |    0 |
+------+------+-----------+--------+------+------+
5 rows in set (0.00 sec)

修改表中某个值
UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

mysql> UPDATE employee SET gao = 180,age = 18,phone = 11222 WHERE name = '4T';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM employee;
+------+------+-------+--------+------+------+
| id   | name | phone | weight | gao  | age  |
+------+------+-------+--------+------+------+
|    1 | Tom  | 11222 |    120 |  180 |   18 |
|    2 | Eric |  NULL |    120 | NULL |    0 |
|    3 | TT   |  NULL |    120 | NULL |    0 |
|    4 | 4T   | 11222 |    120 |  180 |   18 |
|    5 | 5T   |  NULL |    120 | NULL |    0 |
+------+------+-------+--------+------+------+
5 rows in set (0.00 sec)

删除一行:
DELETE FROM 表名字 WHERE 条件;

mysql> DELETE FROM employee WHERE id = 5;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM employee;
+------+------+-------+--------+------+------+
| id   | name | phone | weight | gao  | age  |
+------+------+-------+--------+------+------+
|    1 | Tom  | 11222 |    120 |  180 |   18 |
|    2 | Eric |  NULL |    120 | NULL |    0 |
|    3 | TT   |  NULL |    120 | NULL |    0 |
|    4 | 4T   | 11222 |    120 |  180 |   18 |
+------+------+-------+--------+------+------+
4 rows in set (0.00 sec)

                            高效操作数据库
//建立索引
CREATE INDEX 索引名 ON 表名字 (列名);

mysql> CREATE INDEX EMP_INDEX ON employee(gao);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM employee;
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employee |          1 | EMP_INDEX |            1 | gao         | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

SHOW INDEX FROM 表名字; 查看刚才新建的索引：
mysql> SELECT ID FROM employee WHERE id != 3;
+------+
| ID   |
+------+
|    1 |
|    2 |
|    4 |
+------+
视图
创建视图的语句格式为：
CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

mysql> CREATE VIEW v2(V_ID,V_AGE) AS SELECT id,name FROM employee;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM v2;
+------+-------+
| V_ID | V_AGE |
+------+-------+
|    1 | Tom   |
|    2 | Eric  |
|    3 | TT    |
|    4 | 4T    |
+------+-------+
4 rows in set (0.00 sec)
 
导出：
mysql 终端中查看路径变量：
show variables like '%secure%';

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_jt_Database |
+-----------------------+
| EMPLY                 |
| EMPLY2                |
| NOTN                  |
| ONLY                  |
| PROJECT               |
| SHIT                  |
| employee              |
| v2                    |
| v_emp                 |
| views                 |
+-----------------------+
10 rows in set (0.00 sec)

mysql> SELECT * INTO OUTFILE '/var/lib/mysql-files/outfile.txt' FROM employee;
Query OK, 4 rows affected (0.01 sec)

//查看文件
root@DESKTOP-EOV224Q:/var/lib/mysql-files# ls
outfile.txt
root@DESKTOP-EOV224Q:/var/lib/mysql-files# cat outfile.txt
1       Tom     11222   120     180     3
2       Eric    \N      120     \N      3
3       TT      \N      120     \N      3
4       4T      11222   120     180     3SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt' FROM employee;

导入：
把一个文件里的数据保存进一张表
LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;

文件拷贝到/var/lib/mysql-files下
mysql> LOAD DATA INFILE '/var/lib/mysql-files/infile.txt' INTO TABLE employee;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM employee;
+------+------+-------+--------+------+------+
| id   | name | phone | weight | gao  | age  |
+------+------+-------+--------+------+------+
|    1 | Tom  | 11222 |    120 |  180 |    3 |
|    2 | Eric |  NULL |    120 | NULL |    3 |
|    3 | TT   |  NULL |    120 | NULL |    3 |
|    4 | 4T   | 11222 |    120 |  180 |    3 |
|    6 | Tom  | 11222 |    120 |  180 |    3 |
|    7 | Eric |  NULL |    120 | NULL |    3 |
|    8 | TT   |  NULL |    120 | NULL |    3 |
|    0 | 4T   | 11222 |    120 |  180 |    3 |
|    6 | To   | 11222 |    120 |  180 |    3 |
|    7 | Eri  |  NULL |    120 | NULL |    3 |
|    8 | T    |  NULL |    120 | NULL |    3 |
|    0 | 4    | 11222 |    120 |  180 |    3 |
+------+------+-------+--------+------+------+
12 rows in set (0.00 sec)

备份：
使用 mysqldump 备份的语句：
mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
root@DESKTOP-EOV224Q:/mnt/d/LinuxFile/MySQL# mysqldump -u root jt_Database > jtbeifen.sql;
root@DESKTOP-EOV224Q:/mnt/d/LinuxFile/MySQL# ls
jtbeifen.sql

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表    
root@DESKTOP-EOV224Q:/mnt/d/LinuxFile/MySQL# mysqldump -u root jt_Database > employee;
root@DESKTOP-EOV224Q:/mnt/d/LinuxFile/MySQL# ls
employee  jtbeifen.sql

mysqldump -u root mysql_shiyan > bak.sql;
使用命令 “ls” 可见已经生成备份文件 bak.sql：

恢复：
mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录
CREATE DATABASE test;  #新建一个名为test的数据库
mysql -u root test < jtbeifen.sql //终端操作

查看：
mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录
use test               #连接数据库test
SHOW TABLES;           #查看test数据库的表

如果数据库出问题，可以按照如下重装：

首先删除mysql:

sudo apt-get remove mysql-*


然后清理残留的数据

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

它会跳出一个对话框，你选择yes就好了 
然后安装mysql

sudo apt-get install mysql-client mysql-server 

检查mysql是不是在运行

sudo service mysql status


如果没有运行你可以

sudo service mysql start