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