mysql笔记 <一>

q2009a06z22

　　一、mysql基本管理
　　1.启动mysql
　　/etc/init.d/mysqld是一个shell启动脚本，启动后最终会调用mysqld_safe脚本，最后调用mysqld服务启动mysql,如下:
　　/etc/init.d/mysqld start   或/usr/local/mysql/bin/mysqld_safe &
　　root     26221     1  0 08:55 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid
　　mysql    27071 26221  0 08:55 ?        00:02:26 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysql/mysql.pid --socket=/tmp/mysql.sock --port=3306
　　2.关闭mysql:
　　mysqladmin -uroot shutdown -p123     平滑关闭服务
　　/etc/init.d/mysqld stop    实际是调用上面的命令
　　Kill –USR2 `cat path/pid`  直接杀死pid来关闭服务，不推荐
　　3.连接数据库
　　mysql -uroot -p123 -D test -S /var/lib/mysql/mysql.sock -h 127.0.0.1 -P 3306
　　选项：
　　-u  指定用户
　　-p  指定用户密码
　　-h  指定服务器地址
　　-P  指定端口号
　　-S  指定SOCK路径
　　-D  指定数据库名，直接连接到数据库
　　-C  数据传输压缩
　　-e  非交互式执行SQL语句
　　4.数据库交互式命令
　　\?帮助信息
　　\c清除当前输入语句
　　\r重新连接到服务器，可选数据库和主机
　　\d设置语句分隔符。
　　\e使用$ EDITOR编辑命令。
　　\G发送命令到mysql server，垂直显示结果。
　　\g发送命令到mysql服务器。
　　\h显示此帮助。
　　\n禁用寻呼机，打印到stdout。
　　\t不要写入outfile。
　　\P设置PAGER [to_pager]。通过PAGER打印查询结果。
　　\p打印当前命令。
　　\R更改您的mysql提示符。
　　\q退出mysql。
　　\#重建完成哈希。
　　\.执行SQL脚本文件。获取文件名作为参数。
　　\s从服务器获取状态信息。
　　\！执行系统shell命令。
　　\T设置outfile [to_outfile]。将所有内容附加到给定的文件中。
　　\u使用另一个数据库。将数据库名称作为参数。
　　\C切换到另一个字符集。可能需要处理具有多字节字符集的binlog。
　　\W每个声明后显示警告。
　　\w每个语句后都不要显示警告。
　　\x清除会话上下文。
　　二、mysql数据库变量管理
　　mysql中变量可分为系统变量与状态变量，系统变量配置了mysql的运行环境属性，状态变量显示了msyql运行过程中的状态信息，而系统变量从作用域划分，又可以分为全局变量和会话变量。
　　系统变量：
　　全局级别的变量：global variables，作用域为整个msyql服务器
　　会话级别的变量：session variables,作用域为当前会话。
　　状态变量：
　　状态变量也分为全局级别与会话级别，但是用户无法设置状态变量，只能查看。
　　1.全局系统变量查询
　　全局系统变量查询：
　　show global variables \G;
　　查看字符集相关的全部设置：
　　show global variables like 'character%';
　　查看日志配置是否开启或关闭：
　　show global variables where variable_name like '%log%' and value='off';
　　2.会话系统变量查询
　　show session variables;
　　当前会话字符集查询
　　show session variables like 'character%';
　　注意：有些变量只存在于全局级别中，而有些变量只存在于会话级别中，如果不指明global或session，则表示查看会话级别的变量值。
　　3.使用select查看单个全局系统变量或会话系统变量
　　select @@global.character_set_server;    #查看系统字符集变量
　　select @@session.wait_timeout;     #查看当前会话超时
　　如不指定global或session，@@var_name表示优先从会话级别获取变量值。
　　4.改变变量的值
　　mysql> select @@session.wait_timeout;
　　+------------------------+
　　| @@session.wait_timeout |
　　+------------------------+
　　|                  28800 |
　　+------------------------+
　　1 row in set (0.00 sec)
　　mysql> set @@session.wait_timeout=14400;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> select @@session.wait_timeout;
　　+------------------------+
　　| @@session.wait_timeout |
　　+------------------------+
　　|                  14400 |
　　+------------------------+
　　1 row in set (0.00 sec)
　　set global var_name=value;
　　set @@global.var_name=value;
　　set session var_name=value;
　　set @@session.var_name=value;
　　不指定级别表示默认设置会话级别的变量
　　状态变量对应用户来说是只读的，所以只改变系统变量的值。
　　在修改系统变量的值时并非永久生效的，重启后将失效。
　　运行时修改global级别的变量，对修改之前建立的会话没有影响，仅对修改后新建的会话有效。
　　运行时修改session级别的变量，仅对当前会话有效，且立即生效。
　　如果要使设定永久生效，需要设置配置文件。
　　三、用户帐户管理
　　1.查看当前库中的用户
　　select user,host,authentication_string from mysql.user;
　　+------------+-----------+-------------------------------------------+
　　| user       | host      | authentication_string                     |
　　+------------+-----------+-------------------------------------------+
　　| root       | localhost | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
　　注意：从5.7以后密码字段改为authentication_string
　　host：表示用户能够通过哪些客户端主机IP登录到当前服务器上的mysql服务，host可以为主机名，也可以为IP地址，但是mysql认为主机名和Ip地址属于不同的主机；
　　可以使用通配符：
　　_  表示任意单个字符
　　%   表示任意长度的任意字符
　　2.创建用户
　　1)使用create user命令创建用户
　　mysql> create user 'linux'@'localhost';
　　Query OK, 0 rows affected (0.01 sec)

　　mysql> create user 'linuxse'@'%'>　　Query OK, 0 rows affected (0.00 sec)
　　2)使用grant授权用户时自动创建用户

　　grant all on *.* to 'zzz'@'localhost'>　　3.删除用户
　　注意使用delete删除用户时，不会删除用户授权，所以下次再创建相同的用户时就会报错，可以使用drop删除用户授权，重新创建。
　　mysql> delete from mysql.user where user='zzz' and host='localhost';
　　Query OK, 1 row affected (0.00 sec)
　　mysql> create user 'zzz'@'localhost';
　　ERROR 1396 (HY000): Operation CREATE USER failed for 'zzz'@'localhost'
　　mysql> drop user zzz@localhost;
　　Query OK, 0 rows affected (0.01 sec)
　　mysql> create user 'zzz'@'localhost';
　　Query OK, 0 rows affected (0.01 sec)
　　4.重命名用户
　　rename user OldName to NewName;    指定旧和新用户名
　　mysql> select user,host from mysql.user;
　　+------------+-----------+
　　| user       | host      |
　　+------------+-----------+
　　| linuxse    | %         |
　　| mysql.sys  | localhost |
　　| root       | localhost |
　　+------------+-----------+
　　8 rows in set (0.00 sec)
　　mysql> rename user linuxse to aaaa;
　　Query OK, 0 rows affected (0.01 sec)
　　mysql> select user,host from mysql.user;
　　+------------+-----------+
　　| user       | host      |
　　+------------+-----------+
　　| aaaa       | %         |
　　| mysql.sys  | localhost |
　　| root       | localhost |
　　+------------+-----------+
　　8 rows in set (0.00 sec)
　　5.修改用户密码
　　1)系统命令中修改密码
　　mysqladmin -uzhang -p123 password 123
　　如果没有密码就不用指定密码
　　mysqladmin -uzhang password 123
　　2)登录到mysql修改密码
　　set password for 'zhang'@'%'=password('123');
　　flush privileges;
　　3)修改表字段修改密码，注意5.7以后密码字段为authentication_string
　　mysql> update mysql.user set authentication_string=password('456') where user='zhang' and host='%';
　　Query OK, 1 row affected, 1 warning (0.00 sec)
　　Rows matched: 1  Changed: 1  Warnings: 1
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.01 sec)
　　4)忘记密码修改，重置
　　停止数据库
　　/etc/init.d/mysqld stop
　　绕过用户验证启动mysql
　　/usr/local/mysql/bin/mysqld_safe --skip-grant-tables &
　　登录mysql:
　　mysql -uroot
　　修改用户密码：
　　mysql> update mysql.user set authentication_string=password('456') where user='root' and host='localhost';
　　Query OK, 1 row affected, 1 warning (0.01 sec)
　　Rows matched: 1  Changed: 1  Warnings: 1
　　刷新后退出：
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.01 sec)
　　mysql>\q
　　重新正常启动
　　mysqladmin-uroot -p123 shutdown
　　ps-ef|grep -v grep|grep mysql
　　/etc/init.d/mysqldstart
　　四、mysql授权管理
　　Create: 建立新的数据库或数据表。
　　Delete: 删除表的记录。
　　Drop: 删除数据表或数据库。
　　INDEX: 建立或删除索引。
　　Insert: 增加表的记录。
　　Select: 显示/搜索表的记录。
　　Update: 修改表中已存在的记录。
　　全局管理MySQL用户权限：
　　file: 在MySQL服务器上读写文件。
　　PROCESS: 显示或杀死属于其它用户的服务线程。
　　RELOAD: 重载访问控制表，刷新日志等。
　　SHUTDOWN: 关闭MySQL服务。
　　特别的权限：
　　ALL: 允许做任何事(和root一样)。
　　USAGE: 只允许登录--其它什么也不允许做。
　　1、用户权限
　　授权语法：

　　grant all [privileges] on db.table to 'username'@'host'>　　授权zhang用户对所有库所有表所有权限，并设置密码，允许在任何地方登录

　　mysql> grant all privileges on *.* to 'zhang'@'%'>　　Query OK, 0 rows affected, 1 warning (0.01 sec)
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.01 sec)
　　授权zzz用户对zabbix库中的所有表有insert,delete,update,select权限：
　　mysql> grant insert,delete,update,select on zabbix.* to zzz@'localhost';
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.01 sec)
　　授权用户远程登录时，必须使用SSL建立会话：
　　mysql> grant usage on *.* to 'xin'@'%' require ssl;
　　Query OK, 0 rows affected, 1 warning (0.01 sec)
　　撤销强制使用SSL建立会话的限制：
　　mysql> grant usage on *.* to 'xin'@'%' require none;
　　使用with grant option使授权用户也拥有授予其他用户同样的权限：
　　授权用户xin有select权限，同时它可以授权别的用户同样的权限。
　　grant select on *.* to 'xin'@'%' with grant optiontion;
　　其他限制：
　　MAX_QUERIES_PER_HOUR：限制用户每个小时执行的查询语句次数
　　MAX_UPDATES_PER_HOUR：限制用户每小时执行的更新语句次数
　　MAX_CONNECTIONS_PER_HOUR：限制用户每小时连接数据库的次数
　　MAX_USER_CONNECTIONS:限制用户使用当前帐号同时连接服务器的连接数
　　grant select on *.* to 'xin'@'%' with max_queries_per_hour 20;
　　grant select on *.* to 'xin'@'%' with max_updates_per_hour 10;
　　grant select on *.* to 'xin'@'%' with max_connections_per_hour 15;
　　grant select on *.* to 'xin'@'%' with max_user_connections 2;
　　上面的数据改为0表示不限制。
　　2.查看授权
　　show grants for xin@'%';
　　从数据库中查看用户授权：
　　语法：select * from mysql.db where Db="数据库名"
　　select * from mysql.db where Db='mysql'\G
　　3.删除授权
　　语法：revoke 权限 on 数据库.表 from 用户@host;
　　删除用户创建视图权限：
　　revoke create view on *.* from zhang@'%';
　　五、库管理语句
　　1.创建数据库
　　CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
　　[create_specification] ...
　　create_specification:
　　[DEFAULT] CHARACTER SET [=] charset_name
　　| [DEFAULT] COLLATE [=] collation_name
　　在其他关系型数据库中，database与schema是有区别的，但在mysql 5.0.2以后，我们创建的数据库时可以不加区分的使用database和schema
　　if not exists表示在对应的数据库不存在的时候才会创建，它是可选的。
　　create_specitification表示我们可以在创建数据库时指定对应的数据库规范：
　　可以指定数据库的字符集，使用character set对应的字符集名称，可以指定数据库字符集，如果使用了default关键字，那么这个数据库中创建的所有表默认都继承这个数据库的字符集，可以使用"show character set"命令查看所有可用的字符集，同时我们还可以指定创建数据库时指定数据库的排序规则，排序规则是针对字符集的，每个字符集都有自己默认的排序规则，使用"show collation"命令可以查看所有可用的排序方式，以及排序对应的字符集，default为可选指令，与指定字符集作用相同。
　　创建testdb数据库：
　　mysql> create database testdb;
　　Query OK, 1 row affected (0.00 sec)
　　如果testdb数据库不存在，则创建：
　　mysql> create database if not exists testdb;
　　Query OK, 1 row affected, 1 warning (0.00 sec)
　　如果testdb数据库不存在，则创建，并指定其默认字符集为utf8：
　　mysql> create database if not exists testdb default character set utf8;
　　Query OK, 1 row affected, 1 warning (0.01 sec)
　　创建testdb数据库，指定字符集为utf8并指定排序方式：
　　create database if not exists testdb default character set utf8 collate utf8_general_ci;
　　2.查询数据库
　　查看所有存在的数据库：
　　show databases;
　　查看数据库语句结构：
　　show create database testdb;
　　查看可用的字符集：
　　show character set;
　　查看字符集排序方式：
　　show collation;
　　查看当前数据库与当前连接的概要信息：
　　root@mysql 11:50:50>use mysql
　　Reading table information for completion of table and column names
　　You can turn off this feature to get a quicker startup with -A
　　遇到上面的提示信息，表示mysql要预读数据库信息，如果数据过大会导致连接变慢，可以在连接数据时指定-A连接关闭此功能，也可以在配置文件my.cnf的客户端中添加字段：no-auto-rehash重新加载，关闭预读功能。
　　Database changed
　　root@mysql 11:56:20>status
　　--------------
　　mysql  Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using  EditLine wrapper

　　Connection>　　Current database:mysql  #当前选中使用的数据库
　　Current user:root@localhost #当前连接的登录用户
　　SSL:Not in use   #是否使用了ssl
　　Current pager:stdout #
　　Using outfile:''
　　Using delimiter:; #当前会话行终结符
　　Server version:5.7.16-log Source distribution #版本号
　　Protocol version:10  #协议版本
　　Connection:Localhost via UNIX socket #使用的连接类型，通过本机的套接字文件进行连接
　　Server characterset:utf8   #服务器使用的字符集
　　Db     characterset:utf8  #数据库使用的字符集
　　Client characterset:utf8  #客户端使用的字符集
　　Conn.  characterset:utf8  #当前连接使用的字符集
　　UNIX socket:/tmp/mysql.sock  #套接字文件路径
　　Uptime:23 hours 26 min 5 sec #数据库的启动时长
　　Threads: 17  Questions: 54562  Slow queries: 0  Opens: 1920  Flush tables: 1  Open tables: 99  Queries per second avg: 0.646
　　--------------
　　3.修改数据库
　　ALTER {DATABASE | SCHEMA} [db_name]

　　>　　ALTER {DATABASE | SCHEMA} db_name
　　UPGRADE DATA DIRECTORY NAME
　　alter_specification:
　　[DEFAULT] CHARACTER SET [=] charset_name
　　| [DEFAULT] COLLATE [=] collation_name
　　查看字符集：
　　show  variables like 'character%';
　　修改数据库字符集：
　　alter database testdb character set gbk;
　　alter database testdb default character set gbk;
　　4.删除数据库
　　DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
　　删除testdb数据库：
　　drop database if exists testdb;
　　六、表管理语句
　　1.查看表
　　use mysql
　　show tables;
　　查看表的具体属性信息：
　　show table status\G
　　指定查看某张表的属性：
　　show table status like 'user'\G
　　查看表结构语句：
　　desc table_name
　　desc user\G
　　查看表的创建语句：
　　show create table user\G
　　2.创建表
　　CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
　　(create_definition,...)
　　[table_options]
　　[partition_options]
　　auto_increment设置字段为自增长，此字段必须被定义为key或者索引。默认只设置字段主键为自增
　　primary key(col1[,col2,...])用于定义主键，一个表中只能有一个主键，一个主键可以包含多个字段。
　　unique key(col1[col2,...])用于定义唯一键，一个表中可以有多个唯一键。
　　foreign key用于定义外键
　　chech(expr)用于定义检查性约束
　　index ind_name(id)|key ind_name(id)可以使用两种方法创建索引
　　fulltext|spatial如果数据库存储引擎为myisam可以创建全文索引和空间索引，innodb引擎不支持全文索引和空间索引。
　　实例：
　　创建表：
　　创建数据库指定字符集和排序方式：
　　root@mysql 01:20:53>create database test default character set utf8 collate utf8_general_ci;
　　Query OK, 1 row affected (0.01 sec)
　　root@mysql 01:22:47>use test;
　　创建test表指定字段id和name的类型分别为int和varchare
　　root@mysql 01:23:12>create table test1(

　　->>　　-> name varchar(58)
　　-> );
　　Query OK, 0 rows affected (0.02 sec)
　　在当前字段设置主键，并设置字段不能为空，添加注释字段：
　　root@mysql 01:31:20>create table test2(
　　->>key,
　　-> name varchar(58) not null comment 'student name'
　　-> );
　　Query OK, 0 rows affected (0.02 sec)
　　在表级别定义主键：
　　root@mysql 01:32:28>create table test3(

　　->>　　-> name varchar(34) not null comment 'sutdent name',
　　-> primary key(id)
　　-> );
　　Query OK, 0 rows affected (0.01 sec)
　　创建联合主键：
　　root@mysql 01:34:28>create table test4(id int(10),name varchar(34) not null,primary key(id,name));
　　Query OK, 0 rows affected (0.02 sec)
　　创建索引，可以使用index ind_name()和key ind_name()两种方法创建索引：
　　root@mysql 01:36:24>create table test5(id int(11) auto_increment,name varchar(58) not null,primary key(id),index ind_name(name));
　　Query OK, 0 rows affected (0.03 sec)
　　root@mysql 01:41:39>create table test6(id int(11) auto_increment,name varchar(58) not null,primary key(id),key ind_name(name));
　　Query OK, 0 rows affected (0.02 sec)
　　root@mysql 01:43:44>create table test7(id int(11) auto_increment,name varchar(58) not null,primary key(id),index(name));
　　Query OK, 0 rows affected (0.03 sec)
　　复制表数据：
　　还可以根据查询的数据创建一个新表，将数据填充到创建的表中，需要注意字段中的约束无法复制过去：
　　root@mysql 01:49:31>desc test7;
　　+-------+-------------+------+-----+---------+----------------+
　　| Field | Type        | Null | Key | Default | Extra          |
　　+-------+-------------+------+-----+---------+----------------+

　　|>　　| name  | varchar(58) | NO   | MUL | NULL    |                |
　　+-------+-------------+------+-----+---------+----------------+
　　2 rows in set (0.00 sec)
　　root@mysql 01:49:38>select * from test7;
　　+----+-------+

　　|>　　+----+-------+
　　|  4 | haha  |
　　|  3 | hehe  |
　　|  1 | zhang |
　　|  2 | zhang |
　　+----+-------+
　　4 rows in set (0.00 sec)

　　root@mysql 01:49:56>create table test8 select>　　Query OK, 4 rows affected (0.03 sec)
　　Records: 4  Duplicates: 0  Warnings: 0
　　root@mysql 01:51:26>desc test8;
　　+-------+-------------+------+-----+---------+-------+
　　| Field | Type        | Null | Key | Default | Extra |
　　+-------+-------------+------+-----+---------+-------+

　　|>　　| name  | varchar(58) | NO   |     | NULL    |       |
　　+-------+-------------+------+-----+---------+-------+
　　2 rows in set (0.00 sec)
　　root@mysql 01:51:39>select * from test8;
　　+----+-------+

　　|>　　+----+-------+
　　|  4 | haha  |
　　|  3 | hehe  |
　　|  1 | zhang |
　　|  2 | zhang |
　　+----+-------+
　　4 rows in set (0.00 sec)
　　复制表结构：
　　使用like来将test5的表结构包括字段约束和索引都复制到创建的新表中test9：
　　root@mysql 01:57:19>desc test5;
　　+-------+-------------+------+-----+---------+----------------+
　　| Field | Type        | Null | Key | Default | Extra          |
　　+-------+-------------+------+-----+---------+----------------+

　　|>　　| name  | varchar(58) | NO   | MUL | NULL    |                |
　　+-------+-------------+------+-----+---------+----------------+
　　2 rows in set (0.00 sec)
　　root@mysql 01:57:26>create table test9 like test5;
　　Query OK, 0 rows affected (0.03 sec)
　　root@mysql 01:57:53>desc test9;
　　+-------+-------------+------+-----+---------+----------------+
　　| Field | Type        | Null | Key | Default | Extra          |
　　+-------+-------------+------+-----+---------+----------------+

　　|>　　| name  | varchar(58) | NO   | MUL | NULL    |                |
　　+-------+-------------+------+-----+---------+----------------+
　　2 rows in set (0.00 sec)
　　使用engine选项还指定数据引擎，还可以指定自增长和字符集等：
　　root@mysql 01:58:04>create table test10(id int(11) primary key auto_increment,name varchar(34) not null,index(name))engine=InnoDB auto_increment=2 default
　　Query OK, 0 rows affected (0.03 sec)
　　其他信息可查看帮助信息：
　　root@mysql 02:06:41>help create table;
　　3.删除表
　　drop table table_name;
　　drop table if exists table_name1,table_name2;
　　4.修改表
　　重命名表名：
　　alter table test1 rename sa test2;
　　添加字段：
　　alter table test add column age int(11);
　　修改表的字段，可以省略column：
　　alter table test add age int(11);
　　为表添加字段，同时为添加字段设定约束：
　　alter table test add age int not null default 0;
　　alter table test column age int not null default 0;
　　为表添加字段，同时指定添加字段在表中的位置，下面为添加字段为第一个字段：

　　alter table test add>　　指定添加字段的位置为name字段的后面：
　　alter table test add column age int after name;
　　删除字段
　　alter table test drop name;
　　重命名字段，修改字段名称
　　alter table test change name name1 varchar(48);
　　修改字段类型
　　alter table test change age age char(10);
　　alter table test modify age int;