查看表结构
1、以列表方式显示
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
2、显示建表语句
mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3、查看当前连接的数据库
mysql> select database();
+-------------+
| database() |
+-------------+
| test|
+-------------+
1 row in set (0.00 sec)
数据库编码
1、查看数据库编码
mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| character_set_database | utf8 |
+------------------------+-------+
1 row in set (0.03 sec)
2、修改数据编码
mysql> alter database test character set gbk;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| character_set_database | gbk |
+------------------------+-------+
1 row in set (0.00 sec)
3、修改表编码
mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql>
mysql> alter table test character set gbk;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
常用函数
1、GROUP_CONCAT
同一字段返回多个值时，如果需要将这些值组合在一列，并以指定的字符间隔，使用GROUP_CONCAT，格式GROUP_CONCAT(列明 SEPARATOR 分割符)，示例：
GROUP_CONCAT(name SEPARATOR ',')
2、根据所有表生成sql语句，如生成删除所有表的语句
SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'test';3、from_unixtime
返回Unix时间标记的一个字符串，根据format格式化。如果format为空默认会返回YYYY-MM-DD HH:MM:SS的格式
如果是精确到毫秒的时间戳，则通过from_unixtime返回的值为null，解决方式为：/1000
如时间戳：1649424100794.6677
mysql> select FROM_UNIXTIME(1649424100794.6677) as date;
+------+
| date |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
解决后的结果
mysql> select FROM_UNIXTIME(1649424100794.6677/1000) as date;
+----------------------------+
| date |
+----------------------------+
| 2022-04-08 21:21:40.794668 |
+----------------------------+
1 row in set (0.00 sec)
3、DATE_FORMAT
转换为毫秒时，需要使用%Y-%m-%d %T:%f，且T一定要大写
mysql> select DATE_FORMAT('2022-04-08 21:21:40.794668','%Y-%m-%d %T:%f');
+------------------------------------------------------------+
| DATE_FORMAT('2022-04-08 21:21:40.794668','%Y-%m-%d %T:%f') |
+------------------------------------------------------------+
| 2022-04-08 21:21:40:794668 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
4、now、CURRENT_TIMESTAMP
这两个函数默认返回值精确到秒，如果要精确到毫秒，可以使用NOW(3)、CURRENT_TIMESTAMP(3）
mysql> select now(3);
+-------------------------+
| now(3) |
+-------------------------+
| 2022-04-21 21:05:09.079 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP(3);
+-------------------------+
| CURRENT_TIMESTAMP(3) |
+-------------------------+
| 2022-04-21 21:05:10.277 |
+-------------------------+
1 row in set (0.00 sec)
5、二进制、十进制、八进制、16进制相互转换函数
bin、oct、hex、unhex、conv
详见：mysql怎么进行进制转换-mysql教程-PHP中文网
6、ip地址、二进制相互转换，inet_ntoa、inet_aton函数
详见：mysql中数字进制的转换以及IP地址的处理_liyong635的博客-CSDN博客
7、inet_ntoa函数和inet_aton函数
8、substring_index 拆分字符串为多行
SELECT
substring_index(substring_index('张三,李四,王五,赵六,杨七',',',help_topic_id + 1),',' ,- 1) AS Id
FROM
mysql.help_topic
WHERE
help_topic_id < (length('张三,李四,王五,赵六,杨七') - length(REPLACE ('张三,李四,王五,赵六,杨七', ',', '')) + 1);
mysql如何把字符串拆成多行-mysql教程-PHP中文网
9、判断某列包含某个字符的个数
SELECT
LENGTH( NAME ) - LENGTH( REPLACE ( NAME, ',', '' ) )
FROM USER;修改字符集
1、查看配置文件所在位置：
mysql --verbose --help|grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 2、修改 character_set_client 的字符集
查看字符集：
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.05 sec)
默认情况下，character_set_client、character_set_connection、character_set_results使用字符集latin1，客户端连接mysql时，可以指定字符集，此时mysql服务器将使用指定的字符集设置character_set_client、character_set_connection、character_set_results。
当客户端连接mysql服务器时，它会将自己想要的字符集（比如utf8）发给mysql服务器，mysql服务器就会使用这个字符集去设置：character_set_client、character_set_connection、character_set_results。
如果通过以下两种方式执行sql，然后再通过其它客户端（如SQLyog）连接时，可能会出现乱码或中文字符丢失。这是因为mysql默认的客户端使用的是latin1编码，而SQLyog使用的是utf8编码。
1、sql语句直接拷贝到mysql的命令窗口
mysql> UPDATE test SET value="中国北京" where id=1;
2、通过导入脚本的方式执行：
mysql -uroot -p123456 test < /1.sql此乱码可以通过以下方式解决
1）指定客户端字符集
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> source /1.sql
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2）修改默认字符集
字符集修改后，再通过 mysql -uroot -p123456 test < /1.sql 的方式导入，此时不会再出现乱码，但是直接拷贝可能还是会有乱码
注意：此种方式，可能导致数据库被清空，切记！！！！！！！
在/etc/mysql/mysql.conf.d/mysqld.cnf配置文件中添加：default-character-set=utf8
[root@testlocal]# cat mysql.cnf
[mysql]
default-character-set=utf8
再查看字符集，已修改为了utf8
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
注意：
字符集修改后，客户端可能将无法连接mysql，报错：Access denied for user 'root'@'192.1.10.16' (using password: YES)
解决此问题，需要重新对mysql进行授权：GRANT ALL PRIVILEGES ON *.* TO "root"@"%" IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
授权
查看授权
1、默认查看对localhost的权限
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
2、用户root对指定IP的权限
SHOW GRANTS FOR 'root'@'192.10.10.16';
+--------------------------------------------------------------------------+
| Grants for root@192.10.10.16 |
+--------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.10.10.16' WITH GRANT OPTION |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
3、用户root对localhost的权限
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.09 sec)
或者
mysql> select * from user where user='root' \G;
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *B861587437FDB4434645A38132F3C08DC2051FFC
password_expired: N
password_last_changed: 2022-06-21 11:12:18
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
ERROR:
No query specified
授权
授权、撤销授权后，需要重新连接数据库才会生效。
1、通过grant
如果IP地址为%，则表示允许所有客户端连接
grant all privileges on 库名.表名 to '用户名'@'IP地址' identified by '密码' with grant option;
flush privileges;
1、作用在所有库上
-- 允许任何主机使用用户root和密码mypassword进行连接mysql，且能看到mysql下的所有数据库
-- 第一个*表示数据库名称，第二个*表示数据表名称
-- 允许地址为任意IP的主机使用root用户通过密码mypassword进行连接：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
2、作用在单个数据库上
-- 允许任何主机使用用户root和密码mypassword进行连接mysql，且只能看到mysql下的test数据库
-- 注意，此时必须使用test.*，其中test表示数据库名称，.*表示该数据库下的所有表
GRANT ALL PRIVILEGES ON test.* TO "root"@"%" IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
3、作用在具体的字段上
grant select(id, code, name) on test.student to "root"@"%"; grant all privileges on *.* to root@'%' identified by '123321' with grant option;
或
grant all privileges on test.* to root@'%' identified by '123321' with grant option;
或
grant all privileges on test.* to 'root'@'%.%.%.%' identified by '123321';2、修改user表
1、登录到mysql
mysql -uroot -p123456
2、切换到mysql数据库
use mysql
3、更改权限
update user set host='%' where user='root';3、撤销授权
-- revoke 命令与grant语法基本一样，只需要将to换成from即可
revoke SELECT,update ON `test`.`student` from 'root'@'%';
revoke all ON `mysql`.`test` from 'root'@'%';
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'192.16.15.16';
此时再查询: select * from student;
将会报错：SELECT command denied to user 'root'@'192.18.10.16' for table 'studen'
连接、查看数据库
1、连接
mysql -uroot -p123456 test2、查看当前连接的数据库
mysql> select database();
+-------------+
| database() |
+-------------+
| test|
+-------------+
1 row in set (0.00 sec)
3、查看当前连接的用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)
4、查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |5、切换数据库
use test;6、查看当前数据库的所有表
show tables;7、查看所有存储过程
SHOW PROCEDURE STATUS;8、查看存储过程内容
SELECT body FROM mysql.proc WHERE NAME='pro_test';9、begin end
通常begin-end用于定义一组语句块，在各大数据库中的客户端工具中可直接调用，但在mysql中不可用。
begin-end、流程控制语句、局部变量只能用于函数、存储过程内部、游标、触发器的定义内部。
begin-end语句块在mysql中的使用问题 - 言止予思 - 博客园
10、declare只能用在存储过程、函数中
11、变量
MySQL变量、函数、存储过程的使用 - 咘網鶵釁 - 博客园
mysql用户变量，mysql中用户变量不用提前申明，在用的时候直接用“@变量名”使用就可以了。其作用域为当前连接。
1 -- 第一种用法，使用set时可以用“=”或“:=”两种赋值符号赋值 2 set @age=19; 3 4 set @age:=20; 5 6 -- 第二种用法，使用select时必须用“:=”赋值符号赋值 7 select @age:=22; 8 9 select @age:=StuAge 10 from demo.student 11 where StuNo='A001';
生成批量删除、更新、插入脚本
-- 批量删除，如果需要限定某些表，可以加条件 AND table_name LIKE 'tb_%';
SELECT CONCAT( 'TRUNCATE TABLE ', table_name, ';' ) AS statement FROM information_schema.tables WHERE table_schema = '数据库名称' ;
或
SELECT CONCAT( 'DELETE TABLE ', table_name, ';' ) AS statement FROM information_schema.tables WHERE table_schema = '数据库名称' ;
或
SELECT CONCAT( 'DELETE TABLE ', GROUP_CONCAT(table_name), ';' ) AS statement FROM information_schema.tables WHERE table_schema = '数据库名称' ;
-- 批量更新、插入可以根据实际情况参考以上导入、导出数据库
一、导出数据库
1、导出建库脚本、数据
mysqldump -hlocalhost -uroot -p123456 test > test.sql2、只导出存储过程
mysqldump -hlocalhost -uroot -p123456 -ntd -R test > proc.sql
其中的 -ntd 是表示导出存储过程；-R是表示导出函数3、只导出一个表
mysqldump -hlocalhost -uroot -p123456 test student > student.sql4、只导出表结构
mysqldump -hlocalhost -uroot -p123456 -d --add-drop-table test > test.sql参数说明：
-
-n: --no-create-db
-
-d: --no-data
-
-t: --no-create-info
-
-R: --routines Dump stored routines (functions and procedures)
主要参数介绍：
字符集选项
-
--default--character-set=xx
连接选项
-
-u,--user=name
-
-p,--password=name
-
-h,--host=name
-
-P,--port=#
输出内容选项
-
--add-drop-database
-
--add-drop-table
-
-n;--no-create-db
-
-d;--no-data
-
-t;--no-create-info
输出格式选项
-
--compact
-
-c --complete-insert
-
-T（指定数据表中的数据备份为单纯的数据文件和建表SQL两个文件）
注xx.sql建表文件是以linux的root用户创建，
而xx.txt文件则是一linux的mysql用户创建，
因此这两个文件的存放路径一定要保证mysql用户有读写创建文件的权限。
-
--fields-terminated-by=name（域分隔符）
-
--fields-enclosed-by=name（域引用符）
-
--fields-optionally-enclosed-by=name（域引用可选字符）
-
--fields-escaped-by=name(转义字符)
其他
-
-F --flush-logs（备份前刷新日志）
-
-l --lock-tables(给所有的表加读锁)
二、导入数据库脚本
mysql -hlocalhost -uroot -p123456 test < test.sql存储过程
一、异常处理
1、发生异常时，执行FOR SQLEXCEPTION后面的语句，然后继续执行后面的语句
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _error = 1 ;
2、发生异常时，执行FOR SQLEXCEPTION后面的语句，不在执行后面的语句
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET _error = 1 ;
这两种声明后面都可以跟一个简单语句如：SET _error = 1 ，也可以跟一个Begin End语句块二、事务提交
隐式提交命令会导致事务无法正常回归，存储过程中如果包含create 语句，则在发生异常时，只能回归create 语句后的事务。
A存储过程调用B存储过程，如果B存储过程有commit事务提交，而A存储过程在调用B存储过程后发生异常，则调用B之前的事务不会被回滚。
三、调用存储过程
CALL pro_test('zhangsan', @out_ret, @out_desc);
SELECT @out_ret, @out_desc;四、循环插入1万条数据
-- 创建表
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(20) NOT NULL,
`name` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index1` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=128928 DEFAULT CHARSET=utf8
-- 创建存储过程，插入1000万条
DELIMITER $$
USE `unify_entrance`$$
DROP PROCEDURE IF EXISTS `insertData`$$
CREATE DEFINER=`root`@`%.%.%.%` PROCEDURE `insertData`()
BEGIN
DECLARE
i INT DEFAULT 1 ;
DECLARE str VARCHAR(20);
WHILE i <= 10000000 DO
-- set str = CONCAT('zs', i);
INSERT INTO test (`code`, `name`) VALUES(i, i) ;
SET i = i + 1 ;
END WHILE ;
COMMIT ;
END$$
DELIMITER ;
-- 调用存储过程
call insertData();
未完待续
参考：
MySQL :: MySQL 5.6 Reference Manual :: 10 Character Sets, Collations, Unicode
522
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
