MySql常用操作【基础且详细】
有那么挺长段时间没有敲代码了，今敲起来竟然有些sql都想不起来了😂
把以前整理sql的内容看了下，再加了点😂
主要参考自
MySql文档：https://dev.mysql.com/doc/refman/8.0/en/tutorial.html
易百教程：https://www.yiibai.com/mysql
《MySql必知必会》
一，基本操作
1.连接操作
连接远程数据库:
$ mysql -h host -u -user -p
连接本地数据库：
$ mysql -u user -p
退出:
$ quit
2.状态相关
mysql所处状态的含义 :
| 提示 | 含义 |
|---|---|
mysql> |
准备进行新查询 |
-> |
等待多行查询的下一行 |
'> |
等待下一行，等待以单引号（'）开头的字符串的完成 |
"> |
等待下一行，等待以双引号（"）开头的字符串的完成 |
`> |
等待下一行，等待以反引号（`）开头的标识符的完成 |
/*> |
等待下一行，等待以开头的注释的完成 /* |
取消查询：
mysql> SELECT -> USER() -> \c mysql>
3.数据库相关操作
查询当前服务器下的数据库：
1 mysql> SHOW DATABASES; 2 +----------+ 3 | Database | 4 +----------+ 5 | mysql | 6 | test | 7 | tmp | 8 +----------+
使用数据库：
1 mysql> USE test 2 Database changed
创建数据库：
1 CREATE DATABASE worker;
删除数据库：
1 mysql> DROP DATABASE worker; 2 Query OK, 0 rows affected (0.15 sec)
4.表相关操作
切换到数据库下时，
语法：
1 CREATE TABLE [IF NOT EXISTS] table_name( 2 column_list 3 ) engine=table_type; 4 /* 5 *engine可选InnoDB，MyISAM，HEAP，EXAMPLE等 6 *Mysql5.5后默认为InnoDB 7 */ 8 9 column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] 10 /* 11 *column_name指定列的名称。每列具有特定数据类型和大小，例如：VARCHAR(255)。 12 *NOT NULL或NULL表示该列是否接受NULL值。DEFAULT值用于指定列的默认值。 13 *AUTO_INCREMENT指示每当将新行插入到表中时，列的值会自动增加。 14 */ 15 16 PRIMARY KEY (id) 17 -- 用以设置主键
创建表示例：
1 CREATE TABLE IF NOT EXISTS student ( 2 id INT(8) NOT NULL AUTO_INCREMENT, 3 name VARCHAR(20), 4 number INT(8) DEFAULT NULL, 5 sex CHAR(1), 6 birth DATE, 7 PRIMARY KEY (id) 8 )ENGINE=InnoDB;
创建时复制表结构：
1 CREATE TABLE s3 LIKE student;
创建表时复制表：
1 CREATE TABLE new_table 2 SELECT col, col2, col3 3 FROM 4 existing_table;
创建时复制部分：
1 CREATE TABLE new_table 2 SELECT col1, col2, col3 3 FROM 4 existing_table 5 WHERE 6 conditions;
修改表：
（修改表有重命名表和修改表结构）
1 -- 重命名表 2 ALTER TABLE s1 3 RENAME TO student_bak; 4 -- or 5 RENAME TABLE s1 TO student_bak; 6 7 8 -- 修改表结构 9 10 -- 更改字段 11 ALTER TABLE student 12 CHANGE COLUMN number number INT(8) NOT NULL; 13 14 -- 增加字段 15 ALTER TABLE student 16 ADD COLUMN address VARCHAR(30) NULL 17 AFTER birth; 18 19 -- 删除字段 20 ALTER TABLE student 21 DROP COLUMN sex;
查询库下的表：
1 mysql> SHOW TABLES; 2 +-------------------+ 3 | Tables_in_student | 4 +-------------------+ 5 | student | 6 +-------------------+ 7 1 row in set (0.00 sec)
查看表结构：
1 mysql> DESCRIBE student; 2 +--------+-------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +--------+-------------+------+-----+---------+-------+ 5 | name | varchar(20) | YES | | NULL | | 6 | number | int(8) | YES | | NULL | | 7 | sex | char(1) | YES | | NULL | | 8 | birth | date | YES | | NULL | | 9 +--------+-------------+------+-----+---------+-------+
删除表：
1 mysql> DROP TABLE student;
5.表的CRUD操作
增：
插入数据：
注：
1.尽量使用指定列名的INSER(即方法二),如此在表结构发生改变时，该SQL还能发挥作用。
2.在INSERT操作符中可以省略某些列，但需满足【I.该列定义运行为空值(null)。 II.该列定义中给出默认值】。
3.关于性能,INSERT的操作都时比较耗时的，它们可能降低待处理的SELECT语句的性能，如此可以使用LOW_PRIORITY降低INSERT的优先级。
（如果列的值相对应则可以忽略列名进行插入）
// 方法一：
1 mysql> INSERT INTO student 2 -> VALUES ('Bob',201803224,'m','1999-03-30'); 3 Query OK, 1 row affected (0.44 sec)
// 方法二：
1 mysql> INSERT INTO student 2 -> (name,number,sex,birth) 3 -> VALUES 4 -> ('Lily',201805623,'f','1998-04-23');
插入多条：
1 mysql> INSERT INTO student 2 -> (name,number,sex,birth) 3 -> VALUES 4 -> ('Li',201835623,'f','1998-04-13'), 5 -> ('Mi',201835923,'m','1994-04-03'), 6 -> ('Mark',201845723,'m','1994-03-02'); 7 Query OK, 3 rows affected (0.09 sec) 8 Records: 3 Duplicates: 0 Warnings: 0
插入复制其它表数据进行插入：
1 mysql> INSERT INTO s3 2 -> SELECT name,number,birth,address FROM student; 3 Query OK, 5 rows affected (0.12 sec)
这里要插入的表中也可以指定要插入的列，即s3(列,...)
主键冲突的插入（ON DUPLICATE KEY UPDATE）：
1 INSERT INTO student(id,name,number,birth,address) 2 VALUES (4,'Mi',201835923,'1994-04-03','') 3 ON DUPLICATE KEY UPDATE 4 id = id + 2, 5 address = 'Next Street';
改：
注：
1.UPDATE时要小心不要忘了where子句，否则会更新所有行😂。
2.可以将某个列值更新为null,来去除它的值。
更新数据
1 mysql> UPDATE student 2 -> SET 3 -> address = 'ONE Road', 4 -> name = 'Bon' 5 -> WHERE name = 'Bob';
多表间查询并更新：
-- teacher表 mysql> SELECT * FROM teacher; +----+--------+ | id | name | +----+--------+ | 1 | LiMing | | 2 | WangLu | | 3 | Huali | +----+--------+ 3 rows in set (0.00 sec) -- student表 mysql> SELECT * FROM student; +----+------+-----------+-------------+------------+------------+ | id | name | number | address | birth | teacher_id | +----+------+-----------+-------------+------------+------------+ | 1 | Bon | 201803224 | ONE Road | 1999-03-30 | 1 | | 2 | Lily | 201805623 | NULL | 1998-04-23 | NULL | | 3 | Li | 201835623 | NULL | 1998-04-13 | 2 | | 5 | Mark | 201845723 | NULL | 1994-03-02 | NULL | | 6 | Mi | 201835923 | Next Street | 1994-04-03 | NULL | +----+------+-----------+-------------+------------+------------+ 5 rows in set (0.00 sec) -- 将student中teacher_id空的记录 -- 更新为LiMing的id mysql> UPDATE student -> SET -> teacher_id = (SELECT id -> FROM teacher -> WHERE name = 'LiMing') -> WHERE teacher_id IS NULL;
IGNORE关键字:
更新出错时，UPDATE操作会被取消，回退到错误之前的值，使用IGNORE可以使即使发生错误，也进行更新。
UPDATE IGNORE student ...
删：
注：
1.DELETE语句是删除表中的行，但不删除表本身。
2.若有快速的删除所有行，可以使用TRUNCATE TABLE语句，而非DELETE。
语法
1 DELETE FROM table_name 2 WHERE condition;
删除表中的记录：
-- 删除所有记录 mysql> DELETE FROM student_bak; -- 按条件删除 mysql> DELETE FROM student -> WHERE id = 6; -- 排序后删除限制条数 mysql> DELETE FROM student -> ORDER BY id -> LIMIT 2; Query OK, 2 rows affected (0.07 sec) mysql> SELECT * FROM student; +----+------+-----------+---------+------------+------------+ | id | name | number | address | birth | teacher_id | +----+------+-----------+---------+------------+------------+ | 3 | Li | 201835623 | NULL | 1998-04-13 | 2 | | 5 | Mark | 201845723 | NULL | 1994-03-02 | NULL | +----+------+-----------+---------+------------+------------+ 2 rows in set (0.00 sec)
TRUNCATE TABLE：
其原理是删除原来的表并重新创建一个表，而不是逐行删除表中的数据。
TRUNCATE TABLE s;
查：
语法：
1 SELECT 2 column_1, column_2, ... 3 FROM 4 table_1 5 [INNER | LEFT |RIGHT] JOIN table_2 ON conditions 6 WHERE 7 conditions 8 GROUP BY column_1 9 HAVING group_conditions 10 ORDER BY column_1 11 LIMIT offset, length;
SELECT语句由以下列表中所述的几个子句组成：
SELECT之后是逗号分隔列或星号(*)的列表，表示要返回所有列。FROM指定要查询数据的表或视图。JOIN根据某些连接条件从其他表中获取数据。WHERE过滤结果集中的行。GROUP BY将一组行组合成小分组，并对每个小分组应用聚合函数。HAVING过滤器基于GROUP BY子句定义的小分组。ORDER BY指定用于排序的列的列表。LIMIT限制返回行的数量。
语句中的SELECT和FROM语句是必须的，其他部分是可选的。
简单查询：
mysql> SELECT * FROM student; +----+------+-----------+---------+------------+------------+ | id | name | number | address | birth | teacher_id | +----+------+-----------+---------+------------+------------+ | 3 | Li | 201835623 | NULL | 1998-04-13 | 2 | | 5 | Mark | 201845723 | NULL | 1994-03-02 | NULL | +----+------+-----------+---------+------------+------------+ 2 rows in set (0.00 sec) mysql> SELECT name,address FROM student; +------+---------+ | name | address | +------+---------+ | Li | NULL | | Mark | NULL | +------+---------+ 2 rows in set (0.00 sec)
条件查询：
#查询老师id为2的学生 mysql> SELECT * FROM student -> WHERE teacher_id = 2; +----+------+-----------+------------+------------+------------+ | id | name | number | address | birth | teacher_id | +----+------+-----------+------------+------------+------------+ | 3 | Li | 201835623 | NULL | 1998-04-13 | 2 | | 8 | Lily | 201805623 | 1998-04-23 | NULL | 2 | | 11 | Mary | 201845723 | 1994-03-02 | NULL | 2 | +----+------+-----------+------------+------------+------------+ 3 rows in set (0.00 sec) #查询老师id不为2的学生 mysql> SELECT * FROM student -> WHERE teacher_id <> 2; +----+------+-----------+------------+------------+------------+ | id | name | number | address | birth | teacher_id | +----+------+-----------+------------+------------+------------+ | 5 | Mark | 201845723 | NULL | 1994-03-02 | 1 | | 7 | Bob | 201803224 | 1999-03-30 | NULL | 1 | | 9 | Lim | 201835623 | 1998-04-13 | NULL | 1 | | 10 | Mi | 201835923 | 1994-04-03 | NULL | 3 | +----+------+-----------+------------+------------+------------+ 4 rows in set (0.00 sec)
下表列出了可用于在WHERE子句中形成过滤表达式的比较运算符。
| 操作符 | 描述 |
|---|---|
= |
等于，几乎任何数据类型都可以使用它。 |
<> 或 != |
不等于 |
< |
小于，通常使用数字和日期/时间数据类型。 |
> |
大于， |
<= |
小于或等于 |
>= |
大于或等于 |
