MySQL数据库系统学习（从入门到精通）

MySQL数据库系统学习

一，了解数据库

1.什么是数据库

英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。
顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

2.什么是SQL

SQL：结构化查询语言
程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

3.关于SQL语句的分类

SQL语句有很多，最好进行分门别类，这样更容易记忆。
分为：

1. DQL：
2. 数据查询语言（凡是带有select关键字的都是查询语句）
3. select...
4. DML：
5. 数据操作语言（凡是对表当中的数据进行增删改的都是DML）
6. insert delete update
7. insert 增
8. delete 删
9. update 改
10. 这个主要是操作表中的数据data。
12. DDL：
13. 数据定义语言
14. 凡是带有create、drop、alter的都是DDL。
15. DDL主要操作的是表的结构。不是表中的数据。
16. create：新建，等同于增
17. drop：删除
18. alter：修改
19. 这个增删改和DML不同，这个主要是对表结构进行操作。
21. TCL：
22. 不是王牌电视。
23. 是事务控制语言
24. 包括：
25. 事务提交：commit;
26. 事务回滚：rollback;
28. DCL：
29. 是数据控制语言。
30. 例如：授权grant、撤销权限revoke....

复制代码

4.什么是MySQL

1. MySQL是数据库管理系统中的一种，是市面上最流行的数据库管理软件之一
2. MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
3. MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
4. MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。

复制代码

5.SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾。
• SQL语句可以使用空格/缩进来增强语句的可读性
• MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
• 注释
  • 单行注释：–注释内容或#注释内容（MySQL特有）
  • 多行注释：/* 注释内容 */

二，基本环境使用命令

1.设置root的初始密码(四种方法)

1. mysql_secure_installation #使用MySQL自带的初始化密码命令进行初始化
2. mysqladmin -uroot -p password 123 #使用mysqladmin设置密码
3. 登录数据库：grant all on *.* to root@localhost identified by '123'; #进入数据库来进行修改用户密码
4. 登录数据库：alter user root@localhost identified by '123.com'; #进入数据库来进行修改用户密码

复制代码

2.连接MySQL服务器（三种方式）

1. mysql -uroot -p123 -h 192.168.2.1 -P 3306 #mysql客户端工具进行连接
2. mysql -uroot -p123 -S /tmp/mysql.sock #仅可以本地登录
3. 使用类似navicat工具来进行连接

复制代码

1. MySQL常用选项：
2. -u 用户
3. -p 密码
4. -h IP
5. -P 端口
6. -S socket文件
7. -e 免交互执行命令
8. < 导入SQL脚本

复制代码

3.MySQL的基本操作

1. mysql> create database wordpress default charset utf8mb4 collate utf8mb4_bin; # 创建库
2. Query OK, 1 row affected (0.00 sec)
4. mysql> show create database wordpress; #查看创库语句
5. +-----------+-------------------------------------------------------------------------------------------+
6. | Database | Create Database |
7. +-----------+-------------------------------------------------------------------------------------------+
8. | wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
9. +-----------+-------------------------------------------------------------------------------------------+
10. 1 row in set (0.00 sec)
12. mysql> use wordpress #进入库
13. Database changed
14. mysql> create table t1 (id int primary key,name varchar(20)); #创建表
15. Query OK, 0 rows affected (0.01 sec)
17. mysql> insert into t1 values (1,'zhangsan'),(2,'lisi'),(3,'wuwang'); #插入数据
18. Query OK, 3 rows affected (0.02 sec)
19. Records: 3 Duplicates: 0 Warnings: 0
21. mysql> select * from t1; #查看表内容
22. +----+----------+
23. | id | name |
24. +----+----------+
25. | 1 | zhangsan |
26. | 2 | lisi |
27. | 3 | wuwang |
28. +----+----------+
29. 3 rows in set (0.00 sec)
31. mysql> desc t1; #查看表架构
32. +-------+-------------+------+-----+---------+-------+
33. | Field | Type | Null | Key | Default | Extra |
34. +-------+-------------+------+-----+---------+-------+
35. | id | int(11) | NO | PRI | NULL | |
36. | name | varchar(20) | YES | | NULL | |
37. +-------+-------------+------+-----+---------+-------+
38. 2 rows in set (0.00 sec)
40. mysql> show create table t1; #查看创表语法
41. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
42. | Table | Create Table |
43. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
44. | t1 | CREATE TABLE `t1` (
45. `id` int(11) NOT NULL,
46. `name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
47. PRIMARY KEY (`id`)
48. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
49. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
50. 1 row in set (0.00 sec)

复制代码

4.基本管理命令

1.管理用户

1. mysql> select user,host from mysql.user; #查看mysql中有那些用户
2. +---------------+-----------+
3. | user | host |
4. +---------------+-----------+
5. | mysql.session | localhost |
6. | mysql.sys | localhost |
7. | root | localhost |
8. +---------------+-----------+
9. 3 rows in set (0.00 sec)
11. mysql> create user lisi@'192.168.2.3'; #创建lisi用户
12. Query OK, 0 rows affected (0.00 sec)
14. mysql> select user,host,authentication_string from mysql.user; #查看
15. +---------------+-------------+-------------------------------------------+
16. | user | host | authentication_string |
17. +---------------+-------------+-------------------------------------------+
18. | root | localhost | |
19. | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
20. | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
21. | lisi | 192.168.2.3 | |
22. +---------------+-------------+-------------------------------------------+
23. 4 rows in set (0.01 sec)
25. mysql> alter user lisi@'192.168.2.3' identified by '123.com'; #修改lisi用户密码
26. Query OK, 0 rows affected (0.00 sec)
28. mysql> select user,host,authentication_string from mysql.user; #查看用户表
29. +---------------+-------------+-------------------------------------------+
30. | user | host | authentication_string |
31. +---------------+-------------+-------------------------------------------+
32. | root | localhost | |
33. | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
34. | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
35. | lisi | 192.168.2.3 | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
36. +---------------+-------------+-------------------------------------------+
37. 4 rows in set (0.00 sec)
39. mysql> drop user lisi@'192.168.2.3'; #删除用户
40. Query OK, 0 rows affected (0.00 sec)
42. mysql> select user,host,authentication_string from mysql.user;
43. +---------------+-----------+-------------------------------------------+
44. | user | host | authentication_string |
45. +---------------+-----------+-------------------------------------------+
46. | root | localhost | |
47. | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
48. | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
49. +---------------+-----------+-------------------------------------------+
50. 3 rows in set (0.00 sec)

复制代码

2.管理用户权限

1.创建用户

1. mysql> grant all on wordpress.* to wordpress@'192.168.2.%' identified by '123.com';
2. Query OK, 0 rows affected, 1 warning (0.00 sec)

复制代码

​ 解释：

1. ALL:SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPAC
3. ALL：以上所有权限，一般是普通管理员拥有
4. wordpress.*：wordpress库下的所有表
5. wordpress@'192.168.2.%'：wordpress用户名，只允许192.168.2网段来进行访问 %代表任意
6. identified by '123.com'：设置密码

复制代码

2.查看用户授权

1. mysql> show grants for wordpress@'192.168.2.%';
2. +--------------------------------------------------------------------+
3. | Grants for wordpress@192.168.2.% |
4. +--------------------------------------------------------------------+
5. | GRANT USAGE ON *.* TO 'wordpress'@'192.168.2.%' |
6. | GRANT ALL PRIVILEGES ON `wordpress`.* TO 'wordpress'@'192.168.2.%' |
7. +--------------------------------------------------------------------+
8. 2 rows in set (0.00 sec)

复制代码

3.收回权限

1. mysql> revoke delete on wordpress.* from wordpress@'192.168.2.%';
2. Query OK, 0 rows affected (0.00 sec)
4. mysql> show grants for wordpress@'192.168.2.%';
5. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6. | Grants for wordpress@192.168.2.% |
7. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8. | GRANT USAGE ON *.* TO 'wordpress'@'192.168.2.%' |
9. | GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `wordpress`.* TO 'wordpress'@'192.168.2.%' |
10. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11. 2 rows in set (0.00 sec)

复制代码

5.多实例

1.安装二进制MySQL

1. rpm -e mariadb-libs-5.5.52-1.el7.x86_64 --nodeps
2. tar zxf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
3. mv /usr/local/mysql-5.7.36-linux-glibc2.12-x86_64/ /usr/local/mysql
4. mkdir /usr/local/mysql/data/
5. groupadd mysql && useradd -r -M -s /sbin/nologin -g mysql mysql
6. chown mysql:mysql /usr/local/mysql/ -R
7. ln -s /usr/local/mysql/bin/* /usr/local/bin/
8. yum install -y ncurses-devel libaio-devel autoconf
9. mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
10. vi /etc/my.cnf
11. [mysqld]
12. user=mysql
13. basedir=/usr/local/mysql/
14. datadir=/usr/local/mysql/data/
15. socket=/usr/local/mysql/mysql.sock
16. log_error=/usr/local/mysql/data
17. port=3306
18. server_id=1
19. cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
20. /etc/init.d/mysqld start

复制代码

2.创建多实例文件

1. mkdir -p /usr/local/mysql/330{7,8}/data

复制代码

3.编写MySQL配置文件

1. [root@localhost system]# vi /usr/local/mysql/3307/my.cnf
2. [mysqld]
3. user=mysql
4. basedir=/usr/local/mysql/
5. datadir=/usr/local/mysql/3307/data/
6. socket=/usr/local/mysql/3307/data/mysql.sock
7. log_error=/usr/local/mysql/3307/data/mysql.log
8. port=3307
9. server_id=2
11. [root@localhost system]# vi /usr/local/mysql/3308/my.cnf
12. [mysqld]
13. user=mysql
14. basedir=/usr/local/mysql/
15. datadir=/usr/local/mysql/3308/data/
16. socket=/usr/local/mysql/3308/data/mysql.sock
17. log_error=/usr/local/mysql/3308/data/mysql.log
18. port=3308
19. server_id=3
20. [root@localhost system]# chown mysql:mysql /usr/local/mysql/ -R

复制代码

4.初始化数据库

1. mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/3308/data/
2. mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/3307/data/

复制代码

5.常见系统启动mysql文件

1. [root@localhost system]# cd /etc/systemd/system
2. [root@localhost system]# vi mysqld.service
3. [Unit]
4. Description=MYSQL server
5. After=network.target
6. [Install]
7. WantedBy=multi-user.target
8. [Service]
9. Type=forking
10. TimeoutSec=0
11. PermissionsStartOnly=true
12. ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize $OPTIONS
13. ExecReload=/bin/kill -HUP -$MAINPID #这里-HUP可以是改成-s HUP，就变成强制杀进程，有需要可以改，下面也一样
14. ExecStop=/bin/kill -QUIT $MAINPID #-s QUIT是强制杀进程
15. KillMode=process
16. LimitNOFILE=65535
17. Restart=on-failure
18. RestartSec=10
19. RestartPreventExitStatus=1
20. PrivateTmp=false
21. [root@localhost system]# cp mysqld.service mysqld3307.service
22. [root@localhost system]# vi mysqld3307.service
23. #修改一下数据
24. ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
25. [root@localhost system]# cp mysqld.service mysqld3308.service
26. [root@localhost system]# vi mysqld3308.service
27. #修改一下数据
28. ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf

复制代码

6.启动mysql

1. systemctl daemon-reload
2. systemctl start mysqld3307.service
3. systemctl start mysqld3308.service

复制代码

7.测试

1. [root@localhost system]# netstat -anptl | grep mysqld
2. tcp6 0 0 :::3306 :::* LISTEN 18074/mysqld
3. tcp6 0 0 :::3307 :::* LISTEN 28568/mysqld
4. tcp6 0 0 :::3308 :::* LISTEN 29342/mysqld
5. [root@localhost system]# mysql -S /usr/local/mysql/3307/data/mysql.sock #本地登录
6. Welcome to the MySQL monitor. Commands end with ; or \g.
7. Your MySQL connection id is 2
8. Server version: 5.7.36 MySQL Community Server (GPL)
10. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
12. Oracle is a registered trademark of Oracle Corporation and/or its
13. affiliates. Other names may be trademarks of their respective
14. owners.
16. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
18. mysql> exit
19. Bye

复制代码

三，数据库定义语言（DDL）

1.DDL数据库操作

1.1.库操作

1.1.1.查询

查询所有数据库

1. show databases;

复制代码

查询当前数据库

1.1.2.创建

1. create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]

复制代码

1.1.3.删除

1. drop database [is exists] 数据库名;

复制代码

1.1.4.使用

1. use 数据库名;

复制代码

1.2.表操作

1.2.1.查询当前数据库所有表

1. show table;

复制代码

1.2.2.查询表结构

1. desc 表名;

复制代码

1.2.3.查询指定表的建表语句

1. show create table 表名(
2. 字段1 类型 [comment 注释],
3. 字段2 类型 [comment 注释],
4. 字段3 类型 [comment 注释]
5. )[comment 表注释];

复制代码

1.2.4.删除表

1. drop table 表名;

复制代码

1.2.5.删除指定表，并重新创建表

1. truncate table 表明;

复制代码

1.3.表管理

1.3.1.添加字段

1. alter table 表名 add 字段名 类型 [comment 注释] [约束];

复制代码

1.3.2.修改数据类型

1. alter table 表名 modify 字段名 新类型;

复制代码

1.3.3.修改字段名和字段类型

1. alter table 表名 change 旧字段 新字段 类型 [comment 注释] [约束];

复制代码

1.3.4.删除字段

1. alter table 表名 drop 字段名;

复制代码

1.3.5.修改表名

1. alter table 表名 rename to 新表名;

复制代码

2.管理数据库

1.查看MySQL创建格式

1. mysql> help create database;
2. Name: 'CREATE DATABASE'
3. Description:
4. Syntax:
5. CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
6. [create_option] ...
8. create_option: [DEFAULT] {
9. CHARACTER SET [=] charset_name
10. | COLLATE [=] collation_name
11. }
13. 解释：
14. create：创建语句
15. database：创建数据库
16. schema：同database
17. if not exists：在创建数据库之前进⾏判断，只有该数据库⽬前尚不存在时才能执⾏操作。此选项可以⽤来避免数据库已经存在⽽重复创建的错误
18. db_name：自定义创建数据库名
19. create_option：字符编码选项
20. character set：字符编码
21. collate：校对排序规则
22. create database test default cartacter set=utf8 collate=utf8_bin；

复制代码

2.查看系统支持的字符集

1. mysql> show charset;
2. +----------+---------------------------------+---------------------+--------+
3. | Charset | Description | Default collation | Maxlen |
4. +----------+---------------------------------+---------------------+--------+
5. | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
6. | dec8 | DEC West European | dec8_swedish_ci | 1 |
7. | cp850 | DOS West European | cp850_general_ci | 1 |
8. | hp8 | HP West European | hp8_english_ci | 1 |
9. | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
10. | latin1 | cp1252 West European | latin1_swedish_ci | 1 |
11. | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
12. | swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
13. | ascii | US ASCII | ascii_general_ci | 1 |
14. | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
15. | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
16. | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
17. | tis620 | TIS620 Thai | tis620_thai_ci | 1 |
18. | euckr | EUC-KR Korean | euckr_korean_ci | 2 |
19. | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
20. | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
21. | greek | ISO 8859-7 Greek | greek_general_ci | 1 |
22. | cp1250 | Windows Central European | cp1250_general_ci | 1 |
23. | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
24. | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
25. | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
26. | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
27. | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
28. | cp866 | DOS Russian | cp866_general_ci | 1 |
29. | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
30. | macce | Mac Central European | macce_general_ci | 1 |
31. | macroman | Mac West European | macroman_general_ci | 1 |
32. | cp852 | DOS Central European | cp852_general_ci | 1 |
33. | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
34. | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
35. | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
36. | utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
37. | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
38. | cp1256 | Windows Arabic | cp1256_general_ci | 1 |
39. | cp1257 | Windows Baltic | cp1257_general_ci | 1 |
40. | utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
41. | binary | Binary pseudo charset | binary | 1 |
42. | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
43. | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
44. | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
45. | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
46. +----------+---------------------------------+---------------------+--------+
47. 41 rows in set (0.00 sec)
49. 常用字符集解释：
50. gb2312：简体中文汉字编码国家标准
51. gbk：可以说是对 GB2312 编码的扩充
52. gb18030：完全支持 Unicode，无需动用造字区即可支持中国国内少数民族文字、中日韩和繁体汉字以及 emoji 等字符，编码空间庞大，最多可定义 161 万个字符。
53. Unicode：国际标准字符集
54. utf-8：是一种对Unicode的可变长度字符编码。它可以用一至四个字节对Unicode字符集中的所有有效编码点进行编码，它逐渐成为网络传输中优先采用的编码方式。
55. utf-16：UTF-16 也是一种变长字符编码, 这种编码方式比较特殊, 它将字符编码成 2 字节 或者 4 字节

复制代码

3.创建数据库

1. mysql> create database if not exists test default character set=gb18030 collate=gb18030_bin;
2. Query OK, 1 row affected (0.00 sec)
4. 解释：
5. 创建一个字符集为gb18030排序规则gb18030_bin，名为test，如果存在将跳过创建，不出现报错信息，如果不存在进行创建

复制代码

4.修改数据库字符集

1. mysql> alter database test default character set='utf8' ; #修改库的
2. Query OK, 1 row affected (0.00 sec)
4. mysql> show create database test;
5. +----------+---------------------------------------------------------------+
6. | Database | Create Database |
7. +----------+---------------------------------------------------------------+
8. | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
9. +----------+---------------------------------------------------------------+
10. 1 row in set (0.00 sec)

复制代码

5.修改表存储引擎

1. mysql> show create table test;
2. +-------+-----------------------------------------------------------------------------------------------------------------------------+
3. | Table | Create Table |
4. +-------+-----------------------------------------------------------------------------------------------------------------------------+
5. | test | CREATE TABLE `test` (
6. `id` int(11) DEFAULT NULL,
7. `name` varchar(10) DEFAULT NULL
8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
9. +-------+-----------------------------------------------------------------------------------------------------------------------------+
10. 1 row in set (0.00 sec)
12. mysql> alter table test engine=MyISAM;
13. Query OK, 0 rows affected (0.00 sec)
14. Records: 0 Duplicates: 0 Warnings: 0
16. mysql> show create table test;
17. +-------+-----------------------------------------------------------------------------------------------------------------------------+
18. | Table | Create Table |
19. +-------+-----------------------------------------------------------------------------------------------------------------------------+
20. | test | CREATE TABLE `test` (
21. `id` int(11) DEFAULT NULL,
22. `name` varchar(10) DEFAULT NULL
23. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
24. +-------+-----------------------------------------------------------------------------------------------------------------------------+
25. 1 row in set (0.00 sec)

复制代码

6.删除数据库

1. mysql> drop database test; #表内删除库
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> mysqladmin -u root -p drop test #表外删除库
4. password：

复制代码

3.管理表

1.创建表

1. mysql> create table if not exists txt(id int,name varchar(10) not null,age int not null,primary key(id))engine=innodb default charset=utf8;
2. Query OK, 0 rows affected (0.00 sec)

复制代码

2.查看表结构和类型

1. mysql> desc txt;
2. +-------+-------------+------+-----+---------+-------+
3. | Field | Type | Null | Key | Default | Extra |
4. +-------+-------------+------+-----+---------+-------+
5. | id | int(11) | NO | PRI | NULL | |
6. | name | varchar(10) | NO | | NULL | |
7. | age | int(11) | NO | | NULL | |
8. +-------+-------------+------+-----+---------+-------+
9. 3 rows in set (0.00 sec)
11. mysql> show create table txt;
12. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13. | Table | Create Table |
14. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15. | txt | CREATE TABLE `txt` (
16. `id` int(11) NOT NULL,
17. `name` varchar(10) NOT NULL,
18. `age` int(11) NOT NULL,
19. PRIMARY KEY (`id`)
20. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
21. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22. 1 row in set (0.00 sec)

复制代码

3.插入数据

1. mysql> insert into txt (id,name,age) values(1,'zhangsan',12);
2. Query OK, 1 row affected (0.00 sec)
3. mysql> insert into txt values(2,'lisi',15),(3,'wangwu',18);
4. Query OK, 2 rows affected (0.00 sec)
5. Records: 2 Duplicates: 0 Warnings: 0
6. mysql> select * from txt;
7. +----+----------+-----+
8. | id | name | age |
9. +----+----------+-----+
10. | 1 | zhangsan | 12 |
11. | 2 | lisi | 15 |
12. | 3 | wangwu | 18 |
13. +----+----------+-----+
14. 3 rows in set (0.00 sec)

复制代码

4.对表的字符集进行修改

1. mysql> alter table txt convert to character set gb18030;
2. Query OK, 3 rows affected (0.00 sec)
3. Records: 3 Duplicates: 0 Warnings: 0
5. mysql> show create table txt;
6. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7. | Table | Create Table |
8. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9. | txt | CREATE TABLE `txt` (
10. `id` int(11) NOT NULL,
11. `name` varchar(10) NOT NULL,
12. `age` int(11) NOT NULL,
13. PRIMARY KEY (`id`)
14. ) ENGINE=InnoDB DEFAULT CHARSET=gb18030 |
15. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
16. 1 row in set (0.00 sec)

复制代码

5.对表重命名

1. mysql> alter table a2 rename a3;
2. Query OK, 0 rows affected (0.01 sec)
4. mysql> show tables;
5. +----------------+
6. | Tables_in_test |
7. +----------------+
8. | a3 |
9. +----------------+

复制代码

6.删除表

1. mysql> drop table txt;
2. Query OK, 0 rows affected (0.00 sec)

复制代码

4.对表内数据进行修改

1.查看表内字符集

1. mysql> show full columns from txt;
2. +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
3. | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
4. +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
5. | id | int(11) | NULL | NO | PRI | NULL | | select,insert,update,references | |
6. | name | varchar(10) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
7. | age | int(11) | NULL | NO | | NULL | | select,insert,update,references | |
8. +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

复制代码

2.对表内字段进行修改字符集

1. 格式：alter table 表名 modify 字段 属性 character set 字符集 collate 校验集;

复制代码

1. mysql> alter table txt modify age varchar(50) character set utf8 collate utf8_general_ci;
2. Query OK, 3 rows affected (0.00 sec)
3. Records: 3 Duplicates: 0 Warnings: 0
5. mysql> show full columns from txt;
6. +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
7. | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
8. +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
9. | id | int(11) | NULL | NO | PRI | NULL | | select,insert,update,references | |
10. | name | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
11. | age | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
12. +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
13. 3 rows in set (0.00 sec)

复制代码

3.对字段属性进行修改

1. mysql> alter table txt modify age int;
2. Query OK, 3 rows affected (0.02 sec)
3. Records: 3 Duplicates: 0 Warnings: 0
5. mysql> show full columns from txt;
6. | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
7. +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
8. | id | int(11) | NULL | NO | PRI | NULL | | select,insert,update,references | |
9. | name | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
10. | age | int(11) | NULL | YES | | NULL | | select,insert,update,references | |
11. 3 rows in set (0.00 sec)

复制代码

4.创建字段的属性

1.字段属性

数据类型：	类型
int：	整型
float	浮点型（单精度）
double	浮点型（双精度）
date	日期值（YYYY-MM-DD）
time	时间值（HH:MM:SS）
datetime	混合日期和时间（YYYY-MM-DD hh:mm:ss）
char	定义长字符串
varchar	变长字符字符串

2.其他选项

选项	解释
null	数据可以包含为空的内容
not null	数据列不能为空
default	默认值
[constraint <约束名>] primary key	主键（唯一，不能为空）
primary key (字段1,字段2)	多字段联合主键
foreign key(字段) references 表(字段)	外键
auto_increment	自动递增，适用于整形
unsigned	无符号
character set 字符集	指定字符集
comment	注释

3.使用命令

1. mysql> create table a1 (
2. -> id int primary key auto_increment,
3. -> name varchar(10) not null unique,
4. -> age int default 18,
5. -> stu_id int );
6. Query OK, 0 rows affected (0.00 sec)
8. mysql> create table a2 (
9. -> id int primary key auto_increment,
10. -> name varchar(10),
11. -> foreign key(id) references a1(id));
12. Query OK, 0 rows affected (0.00 sec)

复制代码

5.修改字段内容

1. mysql> update txt set name='zhaoliu' where name='zhangsan';
2. Query OK, 1 row affected (0.00 sec)
3. Rows matched: 1 Changed: 1 Warnings: 0
5. mysql> select * from txt;
6. +----+---------+------+
7. | id | name | age |
8. +----+---------+------+
9. | 1 | zhaoliu | 12 |
10. | 2 | lisi | 15 |
11. | 3 | wangwu | 18 |
12. +----+---------+------+
13. 3 rows in set (0.00 sec)

复制代码

6.删除字段内容

1. mysql> delete from txt where name='zhaoliu';
2. Query OK, 1 row affected (0.00 sec)
4. mysql> select * from txt;
5. +----+--------+------+
6. | id | name | age |
7. +----+--------+------+
8. | 2 | lisi | 15 |
9. | 3 | wangwu | 18 |
10. +----+--------+------+
11. 2 rows in set (0.00 sec)

复制代码

7.查看字段类型

1. mysql> show create table a1;
2. | a1 | CREATE TABLE `a1` (
3. `id` int(11) NOT NULL AUTO_INCREMENT,
4. `name` varchar(10) NOT NULL,
5. `age` int(11) DEFAULT '18',
6. `stu_id` int(11) DEFAULT NULL,
7. PRIMARY KEY (`id`),
8. UNIQUE KEY `name` (`name`)
9. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
10. +-------+---------------------------------------------------------------------------------------------------
11. 1 row in set (0.00 sec)
13. mysql> desc a2;
14. +-------+-------------+------+-----+---------+----------------+
15. | Field | Type | Null | Key | Default | Extra |
16. +-------+-------------+------+-----+---------+----------------+
17. | id | int(11) | NO | PRI | NULL | auto_increment |
18. | name | varchar(10) | YES | | NULL | |
19. +-------+-------------+------+-----+---------+----------------+
20. 2 rows in set (0.00 sec)

复制代码

8.添加字段

1. mysql> alter table a1 add stu_number varchar(10) not null unique;
2. Query OK, 0 rows affected (0.01 sec)
3. Records: 0 Duplicates: 0 Warnings: 0
5. mysql> desc a1;
6. +------------+-------------+------+-----+---------+----------------+
7. | Field | Type | Null | Key | Default | Extra |
8. +------------+-------------+------+-----+---------+----------------+
9. | id | int(11) | NO | PRI | NULL | auto_increment |
10. | name | varchar(10) | NO | UNI | NULL | |
11. | age | int(11) | YES | | 18 | |
12. | stu_id | int(11) | YES | | NULL | |
13. | stu_number | varchar(10) | NO | UNI | NULL | |
14. +------------+-------------+------+-----+---------+----------------+
15. 5 rows in set (0.00 sec)
16. mysql> alter table a1 add stu_number_1 varchar(10) not null unique first; #添加到首行
17. mysql> alter table a1 add stu_number_2 varchar(10) not null unique after id; #添加到id行之后
18. Query OK, 0 rows affected (0.00 sec)
19. Records: 0 Duplicates: 0 Warnings: 0

复制代码

9.删除字段

1. mysql> alter table a1 drop stu_number_1;

复制代码

10.修改字段排序顺序

1. mysql> alter table a1 modify stu_number varchar(10) first;
2. Query OK, 0 rows affected (0.01 sec)
3. Records: 0 Duplicates: 0 Warnings: 0
5. mysql> desc a1;
6. +--------------+-------------+------+-----+---------+----------------+
7. | Field | Type | Null | Key | Default | Extra |
8. +--------------+-------------+------+-----+---------+----------------+
9. | stu_number | varchar(10) | YES | UNI | NULL | |
10. | id | int(11) | NO | PRI | NULL | auto_increment |
11. | stu_number_2 | varchar(10) | NO | UNI | NULL | |
12. | name | varchar(10) | NO | UNI | NULL | |
13. | age | int(11) | YES | | 18 | |
14. | stu_id | int(11) | YES | | NULL | |
15. +--------------+-------------+------+-----+---------+----------------+
16. 6 rows in set (0.00 sec)
18. mysql> alter table a1 modify age int after id ;
19. Query OK, 0 rows affected (0.01 sec)
20. Records: 0 Duplicates: 0 Warnings: 0
22. mysql> desc a1;
23. +--------------+-------------+------+-----+---------+----------------+
24. | Field | Type | Null | Key | Default | Extra |
25. +--------------+-------------+------+-----+---------+----------------+
26. | stu_number | varchar(10) | YES | UNI | NULL | |
27. | id | int(11) | NO | PRI | NULL | auto_increment |
28. | age | int(11) | YES | | NULL | |
29. | stu_number_2 | varchar(10) | NO | UNI | NULL | |
30. | name | varchar(10) | NO | UNI | NULL | |
31. | stu_id | int(11) | YES | | NULL | |
32. +--------------+-------------+------+-----+---------+----------------+
33. 6 rows in set (0.00 sec)

复制代码

11.删除外键约束

1. mysql> create table test4(id int primary key ,name varchar(10),constraint `test` foreign key(id) references test2(id));
2. Query OK, 0 rows affected (0.01 sec)
4. mysql> alter table test4 drop foreign key test;
5. Query OK, 0 rows affected (0.00 sec)
6. Records: 0 Duplicates: 0 Warnings: 0
8. mysql> desc test4;
9. +-------+-------------+------+-----+---------+-------+
10. | Field | Type | Null | Key | Default | Extra |
11. +-------+-------------+------+-----+---------+-------+
12. | id | int(11) | NO | PRI | NULL | |
13. | name | varchar(10) | YES | | NULL | |
14. +-------+-------------+------+-----+---------+-------+
15. 2 rows in set (0.00 sec)

复制代码

四，数据类型和操作符

1.数据类型简介

1. 数值数据类型：TINYINT 、SMALINT 、MEDIUMINT 、INT 、BIGINT 、FLOAT 、DOUBLE 、DECIMAL
2. 日期/时间类型：YEAR 、TIME 、DATE 、DATETIME 、TIMESTAMP
3. 字符串类型：CHAR 、VARCHAR 、BINARY 、VARBINARY 、BLOB 、TEXT 、ENUM 、SET

复制代码

2.数据类型

1.整数类型

类型说明	存储需求	有符号的取值范围	无符号的取值范围
tinyint	1字节	-128-127	0~255
smallint	2字节	-32768-32767	0~65535
mediumint	3字节	-8388608-8388607	0~16777215
int	4字节	-2147483648-2147483647	0~4294967295
bigint	8字节	-9223372036854775808 - 9223372036854775807	0~18446744073709551615

2.浮点型和定点型

类型说明	说明	存储需求	无符号的取值范围
float	单精度浮点型	4字节	3.402823466EE+38 ~ -1.175494251E-38
double	双精度浮点型	8字节	-1.7976931348623157E+308 ~ -2.2250738585072014E-308
decimal	压缩的严格定点数	M+2个字节 不固 定	不固定

3.日期/时间类型

类型说明	存储需求	日期格式	日期范围
year	1字符	YYYY	1901~2155
time	3字符	HH:MM:SS	-838:59:59 ~ 838:59:59
date	3字节	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
datetime	8字节	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp	4字节	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC

4.字符串类型

类型	说明	存储需求
char()	固定长度的文本字符串	M 字节，1 <= M <= 255
varchar()	可变长度的文本字符串	L+1 字节，在此 L <= M 和 1 <= M <= 255
tinytext	非常小的文本字符串	L+1 字节，在此 L < 2^8
text	小的文本字符串	L+2 字节，在此 L < 2^16
mediumtext	中等大小的文本字符串	L+3 字节，在此 L < 2^24
longtext	大的文本字符串	L+4 字节，在此 L < 2^32
enum	枚举类型，只有一个枚举字符串值	1 或 2 个字节，取决于枚举值的数目（最大值 65535）
set	一个设置，字符串对象可以有零个或所得set成员	1, 2, 3, 4 或 8 个字节，取决于集合成员的数量 （最多64个成员）

5.二进制字符串

二进制类型	说明	存储需求
bit(M)	位字段类型	大约（M+7）/8 个字节
binary(M)	固定长度的二进制字符串	M个字节
varbinary(M)	可变长度的二进制字符串	M+1个字节
tinyblob(M)	小的BLOB	L+1 字节，在此 L < 2^8
blob(M)	小的BLOB	L+2 字节，在此 L < 2^16
meidumblob(M)	中等大小的BLOB	L+3 字节，在此 L < 2^24
longblob(M)	非常大的blob	L+4 字节，在此 L < 2^32

3.操作符

1.算数运算符

运算符	作用
+	加法
-	减法
*	乘法
/	除法
%	求余

2.其他运算符

运算符	作用
=	等于
<=>	安全的等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
is null	判断一个值是否为null
is not null	判断一个值是否不为null
least	当有两个或多个参数时，返回最小值
greatest	当有两个或多个参数时，返回最大值
between … and …	判断一个值是否落在两个值之间
in (值1，值2，值3)	判断一个值是否是in列表中的任意一个值
not in (值1，值2，值3)	判断一个值是否不是in列表中的任意一个值
like	通配符匹配
regexp	正则表达式匹配

3.逻辑运算符

运算符	作用
not或!	逻辑非
and或&&	逻辑与
or或||	逻辑或
xor	逻辑异或

4.位运算符

运算符	作用
|	位或
&	位与
^	位异或
<<	位左移
>>	位右移
~	位取反

五，数据库用户权限（DCL）

一，基本用户权限命令

1.创建用户并赋予权限

1. mysql> grant all on test.* to zhangsan@'%' identified by '123.com';
2. Query OK, 0 rows affected, 1 warning (0.00 sec)
3. mysql> show grants for zhangsan@'%';
4. +----------------------------------------------------+
5. | Grants for zhangsan@% |
6. +----------------------------------------------------+
7. | GRANT USAGE ON *.* TO 'zhangsan'@'%' |
8. | GRANT ALL PRIVILEGES ON `test`.* TO 'zhangsan'@'%' |
9. +----------------------------------------------------+
10. 2 rows in set (0.00 sec)

复制代码

2.撤销删除权限

1. mysql> revoke drop,delete on test.* from zhangsan@'%';
2. Query OK, 0 rows affected (0.00 sec)

复制代码

3.查看权限

1. mysql> show grants for zhangsan@'%';
2. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3. | Grants for zhangsan@% |
4. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5. | GRANT USAGE ON *.* TO 'zhangsan'@'%' |
6. | GRANT SELECT, INSERT, UPDATE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'zhangsan'@'%' |
7. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8. 2 rows in set (0.00 sec)

复制代码

六，数据库查询（DQL）

1.select单独使用

1. mysql> select @@basedir; #mysql安装目录
2. mysql> select @@port; #mysql端口号
3. mysql> select @@innodb_flush_log_at_trx_commit; #日志刷新策略
4. mysql> show variables like 'innodb%'; #模糊查看innodb开头的配置
5. mysql> select database(); #查看当前库名
6. mysql> select now(); #查看当前系统时间
7. mysql> select @@server_id; #查看本实例id号，群集中不能重复

复制代码

2.select通用语法

1. select {字段列表} from <表1>,<表2> [where 表达式] [group by] [having] [order by] [limit <>]

复制代码

字段	作用
select	显示的列名（多列逗号分开）
from	表名（多个表逗号分开）
where	过滤条件的列
group by	分组的列
having	分组后的过滤聚合函数
order by	排序的列（desc降序 默认：asc升序）
limit	显示前几行 LIMIT M,N:跳过M行,显示一共N行 LIMIT Y OFFSET X: 跳过X行,显示一共Y行
like %_	模糊查询 %：任意字符 _：一个字符
union	多个结果集合并查询的功能
union all	多个结果集合并查询的功能，去重

通用符：

选项	作用
and	并且
or	或者
in	与or相同
between …and…	在什么范围之内
is not	查询空值
distinct	查询结果不重复

七，数据库操作语言（DML）

1.添加数据

给指定字段添加数据

1. insert into 表名 (字段名1,字段名2) values (值1，值2);

复制代码

给全部字段添加数据

1. insert into 表名 values(值1,值2);

复制代码

批量添加数据

1. insert into 表名 (字段名1,字段名2) values (值1,值2),(值1,值2),(值1,值2);
2. insert into 表名 values (值1,值2),(值1,值2),(值1,值2);

复制代码

注意

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内

2.修改数据

修改数据

1. update 表名 set 字段=值1,字段2=值2 [where 条件];

复制代码

3.删除数据

删除数据

1. delete from 表名 [where 条件]

复制代码

注意

• delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• delete语句不能删除某一个字段的值（可以使用update）

八，MySQL函数

一.数学函数

1. (1) 绝对值函数：ABS(x) 用于返回 x 的绝对值
3. (2) 返回圆周率的函数：PI() PI() 返回圆周率值
5. (3) 平方根函数：SQRT(x) SQRT(x) 返回非负数 x 的二次方根
7. (4) 求余函数：MOD(x,y) MOD(x,y) 返回 x 被 y 除后的余数，MOD(x,y) 对于带有小数部分的数值也起作用，它 返回除法运算后的精确余数
9. (5) 获取整数的函数：CEIL(x) 、CEILING(x) 、FLOOR(x) CEIL(x) 用于返回不小于 x 的最小整数值
11. ​ CEILING(x) 用于返回不小于 x 的最小整数
13. ​ FLOOR(x) 返回不大于 x 的最大整数值
15. (6) 获取随机数的函数：RAND() 、RAND(x) RAND() 用于返回一个随机浮点值，范围在 0 ~ 1 之间
17. ​ RAND(x) 用于返回一个随机浮点值，范围在 0~1 之间，x 被用作种子值，用来产生重复序列
19. (7) 四舍五入的函数：ROUND(x) 、ROUND(x,y) ROUND(x) 用于对 x 进行四舍五入
21. ​ ROUND(x,y) 用于对 x 进行四舍五入，并保留小数点后 y 位
23. (8) 截取数值的函数：TRUNCATE(x,y) TRUNCATE(x,y) 用于对 x 进行截取，结果保留小数点后 y 位
25. (9) 符号函数：SIGN(x) SIGN(x) 用于返回参数 x 的符号，当 x 为负数时返回 -1 ，当 x 为正数时返回 1 ， 当 x 为 零时返回 0
27. (10) 幂运算函数：POW(x,y) 、POWER(x,y) 、EXP(x) POW(x,y) 用于返回 x 的 y 次方的结果
29. (11) 对数运算函数：LOG(x) 、LOG10(x) LOG(x) 返回 x 的自然对数
31. ​ 使用 LOG10(x) 计算以 10 为基数的对数
33. (12) 角度与弧度相互转换的函数：RADIANS(x) 、DEGREES(x) RADIANS(x) 用于将参数 x 由角度转化为弧度
35. ​ DEGREES(x) 用于将参数 x 由弧度转化为角度
37. (13) 正弦函数和反正弦函数：SIN(x) 、ASIN(x) SIN(x) 用于返回 x 的正弦值，其中 x 为弧度值
39. ​ ASIN(x) 用于返回 x 的反正弦，即正弦为 x 的值
41. (14) 余弦函数和反余弦函数：COS(x) 、ACOS(x) COS(x) 用于返回 x 的余弦，其中 x 为弧度值
43. ​ ACOS(x) 用于返回 x 的反余弦，即余弦是 x 的值
45. (15) 正切函数、反正切函数、余切函数：TAN(x) 、ATAN(x) 、COT(x) TAN(x) 返回 x 的正切，其中 x 为给定的弧 度值
47. ​ ATAN(x) 用于返回 x 的反正切，即正切为 x 的值
49. ​ COT(x) 用于返回 x 的余切

复制代码

二，字符串函数

1. (1) 计算字符串长度的函数：CHAR_LENGTH(str) 、LENGTH(str) CHAR_LENGTH(str) 用于统计 str 的字符个数
2. LENGTH(str) 用于统计 str 的字节长度，使用 utf8 编码字符集时，一个汉字是3个字节，一个数字或字母是1个 字节
4. (2) 合并字符串的函数：CONCAT(s1,s2,...) 、CONCAT_WS(x,s1,s2,...) CONCAT(s1,s2,...) 用于合并字符串 s1, s2, ..... ，如果有一个参数为 NULL，则结果为 NULL CONCAT_WS(x,s1,s2,...) 以 x 作为分隔符，将 s1, s2, .... 合并，如果 分隔符为 NULL ，则结果为 NULL
6. (3) 替换字符串的函数：INSERT(s1,x,len,s2) (1) INSERT(s1,x,len,s2) 用于返回字符串 s1，其子字符串起始于 x 位 置和被字符串 s2 取代的 len 字符 (2) 如果 x 超过字符串长度，则返回值为原始字符串，如果 len 的长度大于其 他字符串的长度则从位置 x 开始替换 (3) 如果任何一个参数为 NULL ，则返回值为 NULL
8. (4) 转换大小写的函数：LOWER(str) 、LCASE(str) 、UPPER(str) 、UCASE(str) LOWER(str) 用于将字符串 str 中的 字母字符全部转换成小写字母
10. ​ LCASE(str) 用于将字符串 str 中的字母字符全部转换成小写字母
12. ​ UPPER(str) 用于将 str 中的小写字母转换成大写字母
14. ​ UCASE(str) 用于将 str 中的小写字母转换成大写字母
16. (5) 获取指定长度的字符串的函数：LEFT(s,n) 、RIGHT(s,n) LEFT(s,n) 用于返回字符串 s 开始的最左边 n 个字符
18. ​ RIGHT(s,n) 用于返回字符串 s 中右边 n 个字符
20. (6) 填充字符串的函数：LPAD(s1,len,s2) 、RPAD(s1,len,s2) LPAD(s1,len,s2) 返回字符串 s1 ，其左边由字符串 s2 填充到 len 字符长度，如果 s1 的长度大于 len ，则返回值被缩短至 len 长度
22. ​ RPAD(s1,len,s2) 返回字符串 s1 ，其右边由字符串 s2 填充到 len 字符长度，如果 s1 的长度大于 len ，则返回值 被缩短至 len 长度
24. (7) 删除空格的函数：LTRIM(s) 、RTRIM(s) 、TRIM(s) LTRIM(s) 用于删除字符串 s 左侧的空格
26. ​ RTRIM(s) 用于删除字符串 s 右侧的空格
28. ​ TRIM(s) 用于删除字符串 s 两侧的空格
30. (8) 删除指定字符串的函数：TRIM(s1 FROM s) TRIM(s1 FROM s) 用于删除字符串 s 中两端所有的子字符串 s1 ， 如果没有指定 s1 ，则默认删除字符串 s 两侧的空格
32. (9) 重复生成字符串的函数：REPEAT(s,n) REPEAT(s,n) 用于重复字符串 s ，n 表示重复多少次
34. (10) 空格函数：SPACE(n) SPACE(n) 用于返回 n 个空格
36. (11) 替换函数：REPLACE(s,s1,s2) REPLACE(s,s1,s2) 表示使用字符串 s2 替换字符串 s 中所有的字符串 s1
38. (12) 比较字符串大小的函数：STRCMP(s1,s2) STRCMP(s1,s2) 用于比较字符串 s1 和 s2 的大小，若所有字符串相 同则返回 0 ，若第一个字符串大于第二个字符串则返回 1 ，若第一个字符串小于第二个字符串则返回 -1
40. (13) 获取子字符串的函数：SUBSTRING(s,n,len) 、MID(s,n,len) SUBSTRING(s,n,len) 用于获取指定位置的子字符 串
42. ​ MID(s,n,len) 用于获取指定位置的子字符串
44. (14) 匹配子字符串开始位置的函数：LOCATE(str1,str) 、POSITION(str1 IN str) 、INSTR(str, str1) LOCATE(str1,str) 用于返回字符串 str1 在字符串 str 中的开始位置
46. ​ POSITION(str1 IN str) 用于返回字符串 str1 在字符串 str 中的开始位置
47. ​ INSTR(str, str1) 用于返回子字符串 str1 在字符串 str 中的开始位置
49. (15) 反转字符串的函数：REVERSE(s) REVERSE(s) 用于将字符串 s 反转
51. (16) 返回指定位置的字符串的函数：ELT(n, s1, s2, s3, .....) ELT(n, s1, s2, s3, .....) 用于返回第 n 个字符串，如果 n 超出范围则返回 NULL
53. (17) 返回指定字符串位置的函数：FIELD(s, s1, s2, .....) FIELD(s, s1, s2, .....) 用于返回字符串 s 在列表 s1, s2, .... 中 的位置，如果不存在字符串 s 则返回 0 ，如果字符串 s 是 NULL 也返回 0
54. (18) 返回子字符串位置的函数：FIND_IN_SET(s1, s2) FIND_IN_SET(s1, s2) 用于返回字符串 s1 在字符串列表 s2 中的位置

复制代码

三，日期和时间函数

1. (1) 获取当前日期的函数：CURDATE() 、CURRENT_DATE() CURDATE() 用于获取系统当前日期
2. CURRENT_DATE() 用于系统获取当前日期
3. (2) 获取当前时间的函数：CURTIME() 、CURRENT_TIME() CURTIME() 用于获取系统当前时间
5. (3) 获取当前日期和时间的函数：CURRENT_TIMESTAMP() 、LOCALTIME() 、NOW() 、SYSDATE() CURRENT_TIMESTAMP() 用于获取系统当前日期和时间
7. ​ LOCALTIME() 用于获取系统当前日期和时间
9. ​ NOW() 用于获取系统当前日期和时间
11. ​ SYSDATE() 用于获取系统当前日期和时间
13. (4) 获取时间戳的函数：UNIX_TIMESTAMP() UNIX_TIMESTAMP() 用于获取 UNIX 格式的时间戳
15. (5) 转换时间戳的函数：FROM_UNIXTIME() FROM_UNIXTIME() 用于将 UNIX 格式的时间戳转换为普通格式的时 间
17. (6) 获取 UTC 日期的函数：UTC_DATE() UTC_DATE() 用于获取当前 UTC (世界标准时间) 日期值
19. (7) 获取 UTC 时间的函数：UTC_TIME() UTC_TIME() 用于获取当前 UTC (世界标准时间) 时间值
21. (8) 获取月份的函数：MONTH(date) 、MONTHNAME(date) MONTH(date) 用于返回 date 对应的月份
23. ​ MONTHNAME(date) 用于返回 date 对应月份的英文全名
25. (9) 获取星期的函数：DAYNAME(date) 、DAYOFWEEK(date) 、WEEKDAY(date) 、WEEK(date) 、 WEEKOFYEAR(date) DAYNAME(date) 用于返回 date 对应的工作日的英文名称
27. ​ DAYOFWEEK(date) 用于返回 date 对应的一周中的索引，1 表示周日，2 表示周一，...... ，7 表示周六
29. ​ WEEKDAY(date) 用于返回日期对应的工作日索引，0 表示周一，1 表示周二，...... ，6 表示周日
31. ​ WEEK(date) 用于计算 date 是一年中的第几周，一年有 53 周
33. ​ WEEKOFYEAR(date) 用于计算日期 date 是一年中的第几周，一年有 53 周
35. (10) 获取天数的函数：DAYOFYEAR(date) 、DAYOFMONTH(date) DAYOFYEAR(date) 用于返回 date 是一年中的 第几天，一年有 365 天
37. ​ DAYOFMONTH(date) 用于计算 date 是一个月中的第几天
39. (11) 获取年份的函数：YEAR(date) YEAR(date) 返回 date 对应的年份
41. (12) 获取季度的函数：QUARTER(date) QUARTER(date) 返回 date 对应的一年中的季度值
42. (13) 获取分钟的函数：MINUTE(time) MINUTE(time) 返回 time 对应的分钟值
44. (14) 获取秒钟的函数：SECOND(time) SECOND(time) 返回 time 对应的秒数
46. (15) 获取日期的指定值的函数：EXTRACT(type FROM date) EXTRACT(type FROM date) 用于获取指定的日期值
48. (16) 时间和秒钟转换的函数：TIME_TO_SEC(time) 、SEC_TO_TIME(time) TIME_TO_SEC(time) 用于将 time 转换 为秒钟，公式为 " 小时3600 + 分钟60 + 秒 "
50. ​ SEC_TO_TIME(time) 用于将秒值转换为时间格式
52. (17) 计算日期和时间的函数：DATE_ADD() 、ADDDATE() 、DATE_SUB() 、SUBDATE() 、ADDTIME() 、 SUBTIME() 、DATE_DIFF() DATE_ADD() 用于对日期进行加运算，格式为 DATE_ADD(date, INTERVAL expr type)，expr 与 type 的关系
54. ​ DATE_SUB() 用于对日期进行减运算，格式为 DATE_SUB(date, INTERVAL expr type) ，expr 与 type 的关系
56. ​ SUBDATE() 用于对日期进行减运算，格式为 SUBDATE(date, INTERVAL expr type) ，expr 与 type 的关系
58. ​ ADDTIME() 用于对日期进行加运算，格式为 ADDTIME(date, expr)
60. ​ SUBTIME() 用于对日期进行减运算，格式为 SUBTIME(date, expr)
62. ​ DATE_DIFF() 用于计算两个日期之间的间隔天数
64. (18) 将日期和时间格式化的函数：DATE_FORMAT(date, format) 、TIME_FORMAT(time, format) 、 GET_FORMAT(val_type, format_type) DATE_FORMAT(date, format) 用于格式化日期，即根据 format 指定的格式 显示 date 值，format 格式
66. ​ TIME_FORMAT(time, format) 用于格式化时间，即根据 format 指定的格式显示 time 值，format 格式
68. ​ GET_FORMAT() ，我们指定值类型和格式化类型，然后会显示成格式字符串

复制代码

四.条件判断函数

1. (1) IF() IF(expr, v1, v2) 如果表达式 expr 为 TRUE ，则返回值为 v1 ，否则返回 v2
3. (2) IFNULL() IFNULL(v1, v2) ，如果 v1 不为 NULL ，则返回值为 v1 ；如果 v1 为 NULL ，则返回值为 v2
5. (3) CASE 语法：CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END 含义：如果 expr 等于某个 vn ，则返回对应位置 THEN 后面的结果，如果与所有值都不相等，则返回 ELSE 后面的 rn

复制代码

五.系统信息函数

1. (1) 获取 MySQL 版本号的函数：VERSION() VERSION() 用于获取 MySQL 版本号
3. (2) 查看当前用户的连接数的ID函数：CONNECTION_ID() CONNECTION_ID() 用于查看当前用户的连接数
4. 1. Id ：用户登录 MySQL 时，系统分配的连接 id
5. 2. User ：当前连接的用户
6. 3. Host ：显示这个语句是从哪个 IP 的哪个端口上发出的，可以用来追踪出现问题语句的用户
7. 3. db ：显示这个进程目前连接的是哪个数据库
8. 4. Command ：显示当前连接执行的命令，一般取值为休眠(Sleep)、查询(Query)、连接(Connect)
9. 5. Time ：显示这个状态持续的时间，单位是秒
10. 6. State ：显示使用当前连接的 SQL 语句的状态
11. 7. info ：显示这个 SQL 语句
13. (3) 查看当前使用的数据库的函数：DATABASE() 、SCHEMA() DATABASE() 用于查看当前使用的数据库
14. SCHEMA() 用于查看当前使用的数据库
16. (4) 查看当前登录的用户名的函数：USER() 、CURRENT_USER() 、SYSTEM_USER() USER() 返回当前登录的用户
17. 及主机名
18. CURRENT_USER() 用于返回当前登录的用户及主机名
19. SYSTEM_USER() 用于返回当前登录的用户及主机名
21. (5) 查看指定字符串的字符集的函数：CHARSET(str) CHARSET(str) 用于查看字符串 str 的字符集
22. (6) 查看指定字符串的排列方式的函数：COLLATION(str) COLLATION(str) 用于查看字符串 str 的字符排列方式
23. (7) 获取最后一个自动生成的 ID 值得函数：LAST_INSERT_ID() LAST_INSERT_ID() 用于获取最后一个自动生成的
24. ID 值

复制代码

六.加密/解密函数

1. (1) 加密函数：PASSWORD(str) 、MD5(str) 、ENCODE(str, pswd_str) PASSWORD(str) 从明文密码 str 计算并返 回加密后的密码字符串，当参数为 NULL 时，返回 NULL
3. ​ MD5(str) 为字符串 str 算出一个 MD5 128 比特校验和
5. ​ ENCODE(str, pswd_str) 使用 pswd_str 作为密码，加密 str
7. (2) 解密函数：DECODE(crypt_str, pswd_str) DECODE(crypt_str, pswd_str) 使用 pswd_str 作为密码，解密加密字 符串 crypt_str

复制代码

七.其他函数

1. (1) 格式化函数：FORMAT(x, n) FORMAT(x, n) 将数字 x 格式化，并以四舍五入的方式保留小数点后 n 位，结果 以字符串的形式返回
3. (2) 不同进制的数字进行转换的函数：CONV() CONV() 用于不同进制数之间的转换
5. (3) IP 地址与数字互相转换的函数：INET_ATON(expr) 、INET_NTOA(expr) INET_ATON(expr) 用于将网络地址转 换为一个代表该地址数值的整数
7. (4) 加锁函数和解锁函数：GET_LOCK(str, timeout) 、RELEASE_LOCAK(str) 、 IS_FREE_LOCK(str) 、 IS_USED_LOCK(str) GET_LOCK(str, timeout) 使用字符串 str 来得到一个锁，持续时间 timeout 秒
9. 1. 若成功得到锁，则返回 1
10. 2. 若操作超时，则返回 0
11. 3. 若发生错误，则返回 NULL
13. ​ RELEASE_LOCAK(str) 用于解开被 GET_LOCK() 获取的，用字符串 str 所命名的锁
15. 1. 若锁被解开，则返回 1
16. 2. 若该线程尚未创建锁，则返回 0
17. 3. 若命名的锁不存在，则返回 NULL
18. 4. 若该锁从未被 GET_LOCK() 的调用获取，或锁已经被提前解开，则该锁不存在
20. ​ IS_FREE_LOCK(str) 检查名为 str 的锁是否可以使用
22. 1. 若锁可以使用，则返回 1
23. 2. 若锁正在被使用，则返回 0
24. 3. 若出现错误，则返回 NULL
26. ​ IS_USED_LOCK(str) 用于检查名为 str 的锁是否正在被使用，若被封锁，则返回使用该锁的客户端的连接标识 符，否则返回 NULL
28. (5) 重复执行指定操作的函数：BENCHMARK(count, expr) BENCHMARK(count, expr) 用于重复 count 次执行表 达式 expr
30. 1. 可以用于计算 MySQL 处理表达式的速度
31. 2. 可以在 MySQL 客户端内部报告语句执行的时间
33. (6) 改变字符集的函数：CONVERT(... USING ...) CONVERT(... USING ...) 用于改变字符串的默认字符集
35. (7) 改变数据类型的函数：CAST(x, AS type) 、CONVERT(x, type) CAST(x, AS type) 用于将一个数据类型的值转换 为另一个数据类型的值 CONVERT(x, type) 用于将一个数据类型的值转换为另一个数据类型的值

复制代码

MySQL常见报错

1.MySQL忘记密码

解决1：

1. systemctl stop mysqld
2. mysqld_safe --skip-grant-tables & #跳过权限表
3. mysql
4. grant all on *.* to root@localhost identified by '123456'; #会报错
5. flush privileges;
6. grant all on *.* to root@localhost identified by '123456';
7. exit
8. killall -9 mysqld_safe
9. systemctl start mysqld

复制代码

解决2：

1.在[mysqld]下添加skip-grant-tables，保存即可。

1. mysql stop
2. vi /etc/my.cnf
3. skip-grant-tables

复制代码

2.登录，修改密码

1. mysql -u root -p
2. 不用输入密码，直接回车（出现Enter Password 也一样直接回车，即可登陆成功）
4. update user set authentication_string=password(‘新密码’) where user=‘root’;
5. flush privileges;

复制代码

3.重启mysql解决

1. mysql start

复制代码