2018-08-01Database17 分钟读完 (大约 2517 个字)

MySQL

There is recorded some MySQL operation.

Windows下连接和启动MySQL

mysql -u root -p
将MySql的安装目录下的bin目录设置到PATH环境变量下，打开CMD就能直接连接MySQL了。CMD输入mysql -u root -p，root为用户名，回车后输入密码即可进入到MySQL命令行模式
net start mysql57
运行管理员权限的CMD，输入命令即可启动MySQL
net stop mysql57
运行管理员权限的CMD，输入命令即可关闭MySQL

本文使用的部分单词说明

db_name：数据库名称
table_name：数据表名称
old_name：要修改的字段的名称
new_name：要修改字段的新名称
column_name：列的名称
type：类型和范围，例如：varchar(2)
view_name：视图名称

数据库命令行操作

显示现有数据库
show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| fruitdb            |
| mysql              |
| performance_schema |
| sys                |
| test               |
| wehelp_test        |
+--------------------+
7 rows in set (0.01 sec)

新建数据库
create database db_name;

1 2	mysql> create database fruitsb; Query OK, 1 row affected (0.00 sec)

新建指定字符集类型数据库
create database db_name character set utf8;

1 2	mysql> create database test1 character set utf8; Query OK, 1 row affected (0.00 sec)

删除数据库
drop database db_name;

1 2	mysql> drop database fruitsb; Query OK, 0 rows affected (0.00 sec)

修改数据库字符集
alter database db_name character set utf8;

1 2	mysql> alter database test1 character set utf8; Query OK, 1 row affected (0.00 sec)

切换当前使用的数据库
use db_name;
1
2
mysql> use test1;
Database changed

显示数据库的创建语句
show create table table_name;

mysql> show create table SCORE;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| SCORE | CREATE TABLE `SCORE` (
`SNO` varchar(3) NOT NULL,
`CNO` varchar(5) NOT NULL,
`DEGREE` decimal(10,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改密码

1
2
3

mysql -uroot -p
update user set authentication_string=password("123456") where User='root';
flush privileges;

数据表命令行操作

创建数据表

use my_test
create table test(
id int auto_increment primary key,
age int);	
Query OK, 0 rows affected (0.02 sec)

显示当前数据库的所有数据表
show tables;

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test            |
| test2           |
+-----------------+
2 rows in set (0.00 sec)

显示数据表结构
desc table_name;

mysql> desc test;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| name  | int(11) | NO   | PRI | NULL    | auto_increment |
| age   | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

添加字段
alter table table_name add column_name type;

1
2
3

mysql> alter table test add sex int(2);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除字段
alter table table_name drop column_name;

mysql> alter table test drop sex;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| name  | int(11) | NO   | PRI | NULL    | auto_increment |
| age   | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

修改字段
alter table table_name modify column_name type;

1
2
3

mysql> alter table test modify age varchar(2);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改字段名称
alter table table_name change column old_name new_name type;

mysql> alter table test change column name id int;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| age   | varchar(2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改表名

mysql> ALTER TABLE T_TEST RENAME TO TT;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| TT             |
+----------------+
1 row in set (0.00 sec)

SELECT语句

查询所有数据：select * from table_name;

mysql> select * from test;
+----+------+
| id | age  |
+----+------+
|  1 | 男   |
|  2 | 女   |
|  3 | 中   |
+----+------+
3 rows in set (0.00 sec)

查询指定列数据：select column_name1, column_name2, ..., column_nameN from table_name;

mysql> select age from test;
+------+
| age  |
+------+
| 男   |
| 女   |
| 中   |
+------+
3 rows in set (0.00 sec)

INSERT语句

添加数据行
insert into table_name values(value1, value2, ..., valueN);
insert into table_name(column_name1, column_name2, ..., column_nameN) values(value1, value2, ..., valueN);

mysql> insert into test(id,age) value(4,'未知');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+--------+
| id | age    |
+----+--------+
|  1 | 男     |
|  2 | 女     |
|  3 | 中     |
|  4 | 未知   |
+----+--------+
4 rows in set (0.00 sec)

添加多条数据

insert into table_name(column_name1, column_name2, ..., column_nameN) values(value1, value2, ..., valueN), (value1, value2, ..., valueN), ..., (value1, value2, ..., valueN);

mysql> insert into test value(1,'男'),(2,'女'),(3,'中');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+
| id | age  |
+----+------+
|  1 | 男   |
|  2 | 女   |
|  3 | 中   |
+----+------+
3 rows in set (0.00 sec)

WHERE子句

可用于SELECT、UPDATE、DELETE语句，可以使用AND或者OR指定一个或多个条件来自
（图片来自菜鸟教程）

指定查询的参数值：select * from table_name where column_name=value;

mysql> select * from test where age='未知';
+----+--------+
| id | age    |
+----+--------+
|  4 | 未知   |
+----+--------+
1 row in set (0.00 sec)

多条件指定查询：select * from table_name [where condition1 [and [or]] condition2.....;

mysql> select * from test where id > 2 and id < 5;
+----+--------+
| id | age    |
+----+--------+
|  3 | 中     |
|  4 | 未知   |
+----+--------+
2 rows in set (0.00 sec)

UPDATE语句

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。.

语法
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
- 你可以同时更新一个或多个字段。
- 你可以在 WHERE 子句中指定任何条件。
- 你可以在一个单独表中同时更新数据。

示例：

mysql> update test set age='xx' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+--------+
| id | age    |
+----+--------+
|  1 | 男     |
|  2 | xx     |
|  3 | 中     |
|  4 | 未知   |
+----+--------+
4 rows in set (0.00 sec)

DELETE语句

删除数据的通用语法：delete from table_name [where Clause];

删除一条数据示例

mysql> delete from test where id=2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+--------+
| id | age    |
+----+--------+
|  1 | 男     |
|  3 | 中     |
|  4 | 未知   |
+----+--------+
3 rows in set (0.00 sec)

LIKE子句

可以用来做模糊查询
语法
以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：
1
2
3
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
- 你可以在 WHERE 子句中指定任何条件
- 你可以在 WHERE 子句中使用LIKE子句
- 你可以使用LIKE子句代替等号 =
- LIKE 通常与 % 一同使用，类似于一个元字符的搜索
- 你可以使用 AND 或者 OR 指定一个或多个条件
- 你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件
- 使用’__’指定位数进行精确查询，一个下划线代表一位
  （本段内容来自菜鸟教程)

使用示例：

# 查询age字段中以‘知’结尾的数据行
mysql> select * from test where age like '%知';
+----+--------+
| id | age    |
+----+--------+
|  4 | 未知   |
+----+--------+
1 row in set (0.00 sec)

精确查询示例：

# 查询name字段中以Tom开头后面还有4个字符的数据行
mysql> select * from test where name like 'Tom____';
+------+------+---------+
| id   | age  | name    |
+------+------+---------+
|    3 | 24   | TomCook |
+------+------+---------+
1 row in set (0.00 sec)

ORDER BY子句

当我们需对读取的数据进行排序就可以使用ORDER BY子句

语法
以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：
1
2
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
- 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
- 你可以设定多个字段来排序。
- 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。
- 你可以添加 WHERE…LIKE 子句来设置条件。
  （本段内容来自菜鸟教程）

使用示例：

mysql> select * from test order by id desc;
+----+--------+
| id | age    |
+----+--------+
|  4 | 未知   |
|  3 | 中     |
|  1 | 男     |
+----+--------+
3 rows in set (0.00 sec)

GROUP BY语句

GROUP BY语句根据一个或多个列对结果集进行分组

语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

（本段内容来自菜鸟教程）

使用示例：

mysql> select * from test;
+----+--------+
| id | age    |
+----+--------+
|  1 | 男     |
|  3 | 中     |
|  4 | 未知   |
|  7 | a      |
|  8 | a      |
|  9 | b      |
| 10 | 男     |
+----+--------+
7 rows in set (0.00 sec)

mysql> select age from test group by age;
+--------+
| age    |
+--------+
| a      |
| b      |
| 中     |
| 未知   |
| 男     |
+--------+
5 rows in set (0.00 sec)

BETWEEN语句

BETWEEN是闭区间x BETWEEN 3 AND 4 (3 <= x <= 4)
语法：where column_name between value1 and value2、where column_name not between value1 and value2

示例：

mysql> select * from test where id between 2 and 10;
+----+--------+
| id | age    |
+----+--------+
|  3 | 中     |
|  4 | 未知   |
|  7 | a      |
|  8 | a      |
|  9 | b      |
| 10 | 男     |
+----+--------+
6 rows in set (0.00 sec)

内置方法

floor
向下取整
round
四舍五入
ceil
向上取整

视图

创建视图
CREATE VIEW VIEW_NAME AS SELECT COLUMN_NAME1,COLUMN_NAME2... FROM TABLE_NAME;
1
2
mysql> CREATE VIEW V_SCORE AS SELECT * FROM SCORE;
Query OK, 0 rows affected (0.03 sec)

读取视图数据
SELECT COLUMN_NAME1,COLUMN_NAME2... FROM VIEW_NAME;

mysql> SELECT * FROM V_SCORE;
+-----+-------+--------+
| SNO | CNO   | DEGREE |
+-----+-------+--------+
| 103 | 3-245 |   86.0 |
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 107 | 3-105 |   91.0 |
| 108 | 3-105 |   78.0 |
| 101 | 6-166 |   85.0 |
| 107 | 6-106 |   79.0 |
| 108 | 6-166 |   81.0 |
+-----+-------+--------+
12 rows in set (0.00 sec)

别名创建视图
CREATE VIEW VIEW_NAME (ALIAS_NAME1, ALIAS_NAME2...) AS SELECT COLUMN_NAME1, COLUMN_NAME2... FROM TABLE_NAME;

mysql> CREATE VIEW V_STUDENT(NAME, SEX) AS SELECT SNAME, SSEX FROM STUDENT;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM V_STUDENT;
+--------+-----+
| NAME   | SEX |
+--------+-----+
| 曾华   | 男  |
| 匡明   | 男  |
| 王丽   | 女  |
| 李军   | 男  |
| 王芳   | 女  |
| 陆君   | 男  |
+--------+-----+
6 rows in set (0.00 sec)

视图修改
修改视图数据会影响主表数据，因为视图数据就是从主表读取的，修改视图数据相当于直接修改主表

CREATE OR REPLACE VIEW VIEW_NAME (COLUMN_NAME1, COLUMN_NAME2...) AS SELECT COLUMN_NAME1, COLUMN_NAME2... FROM TABLE_NAME;

mysql> CREATE OR REPLACE VIEW V_SCORE (SNO, CNO) AS SELECT SNO, CNO FROM SCORE;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM V_SCORE;
+-----+-------+
| SNO | CNO   |
+-----+-------+
| 103 | 3-245 |
| 105 | 3-245 |
| 109 | 3-245 |
| 103 | 3-105 |
| 105 | 3-105 |
| 109 | 3-105 |
| 101 | 3-105 |
| 107 | 3-105 |
| 108 | 3-105 |
| 101 | 6-166 |
| 107 | 6-106 |
| 108 | 6-166 |
+-----+-------+
12 rows in set (0.00 sec)

删除视图
DROP VIEW VIEW_NAME

1 2	mysql> DROP VIEW V_SCORE; Query OK, 0 rows affected (0.00 sec)

显示视图详情
DESC VIEW_NAME;

mysql> DESC V_SCORE;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| SNO    | varchar(3)    | NO   |     | NULL    |       |
| CNO    | varchar(5)    | NO   |     | NULL    |       |
| DEGREE | decimal(10,1) | NO   |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MySQL执行语句返回结果

`FOUND_ROWS()`

FOUND_ROWS()函数可以返回查询到的记录数目
A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include an SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward

1
2
3

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

Example

mysql> select * from t_fruits;
+----+------------+-------+-------+--------+
| id | name       | price | count | remark |
+----+------------+-------+-------+--------+
|  2 | orange     |   2.0 |     2 | 橙子   |
|  3 | watermelon |   3.0 |     3 | 西瓜   |
|  4 | longan     |   4.0 |     4 | 龙眼   |
|  5 | kiwis      |   2.2 |   100 |        |
+----+------------+-------+-------+--------+
4 rows in set (0.00 sec)

mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

`ROW_COUNT()`

ROW_COUNT()函数可以返回受到影响的行数

DDL statements: 0. This applies to statements such as CREATE TABLE or DROP TABLE.
DML statements other than SELECT: The number of affected rows. This applies to statements such as UPDATE, INSERT, or DELETE (as before), but now also to statements such as ALTER TABLE and LOAD DATA INFILE(
SELECT: -1 if the statement returns a result set, or the number of rows “affected” if it does not. For example, for SELECT * FROM t1, ROW_COUNT() returns -1. For SELECT * FROM t1 INTO OUTFILE '*file_name*', ROW_COUNT() returns the number of rows written to the file.
SIGNAL statements: 0

Example

mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE i IN(1,2);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

本节内容英语说明来自官方文档

#Notes MySQL

本文标题：MySQL
本文作者：Thomas Woo
发布时间：2018-08-01 16:36
最后更新：2020-06-20 15:40
本文链接：http://smileorigin.site/database/mysql/mysql/
版权声明：本博客所有文章除特别声明外，均采用 CC BY 4.0 许可协议。转载请注明出处！

MySQL

Windows下连接和启动MySQL

本文使用的部分单词说明

数据库命令行操作

数据表命令行操作

SELECT语句

INSERT语句

WHERE子句

UPDATE语句

DELETE语句

LIKE子句

ORDER BY子句

GROUP BY语句

BETWEEN语句

内置方法

视图

MySQL执行语句返回结果

`FOUND_ROWS()`

`ROW_COUNT()`

喜欢这篇文章？打赏一下作者吧

评论

目录

Your browser is out-of-date!