1.2 MySQL用户管理及SQL语句详解

http://www.cnblogs.com/clsn/p/8047028.html
MySQL用户管理及SQL语句详解

（一）基本的MySQL安全配置:
1. 为root用户设置密码
//设置密码
[root@crmn ~]# mysqladmin -uroot password 'oldboy123'//为root用户设置密码oldboy123
[root@crmn ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
//登录
[root@crmn ~]# mysql -uroot -poldboy123 //法一
[root@crmn ~]# mysql -uroot -p //法二
Enter password: //oldboy123
......
mysql>

//忘记密码从而修改密码：
[root@crmn ~]# /etc/init.d/mysqld start
Starting MySQL [确定]
[root@crmn ~]# netstat -lntup|grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3730/mysqld
[root@crmn ~]# mysql -uroot -poldboy123
......
mysql> //步骤如下：
[root@crmn ~]# /etc/init.d/mysqld stop //1.将服务停掉
Shutting down MySQL. [确定]
[root@crmn ~]# netstat -lntup|grep mysqld
[root@crmn ~]# cd /application/mysql/bin/ //2.加参数启动服务
[root@crmn bin]# mysqld_safe --skip-grant-table --user=mysql --skip-networking &
[1] 20363
[root@crmn bin]# 171222 10:55:22 mysqld_safe Logging to '/application/mysql-5.5.32/data/crmn.err'.
171222 10:55:22 mysqld_safe Starting mysqld daemon with databases from /application/mysql-5.5.32/data
//这种模式下:
　　　　无密码登陆
　　　　网络用户无法登陆
　　　　只能本地登陆
　　　　和授权有关的命令都无法执行
MYSQL5.7 修改密码修改字段 authentication_string
[root@crmn bin]# mysql //3.修改密码
......
mysql> update mysql.user set password=password('crmn123') where user='root' and host='localhost';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> quit
Bye
[root@crmn bin]# /etc/init.d/mysqld restart //4.退出重启服务
Shutting down MySQL.171222 10:58:25 mysqld_safe mysqld from pid file /application/mysql-5.5.32/data/crmn.pid ended
[确定]
Starting MySQL.. [确定]
[1]+ Done mysqld_safe --skip-grant-table --user=mysql --skip-networking
[root@crmn bin]# mysql -u root -p crmn123 //5.登录验证
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@crmn bin]# mysql -u root -pcrmn123
......
mysql>
至此密码修改成功!

mysql> drop user root@'::1'; //删除用户
Query OK, 0 rows affected (0.00 sec)

mysql> drop user root@'crmn';
ERROR 1396 (HY000): Operation DROP USER failed for 'root'@'crmn'
mysql> drop user ''@'crmn';
Query OK, 0 rows affected (0.01 sec)

mysql> drop user ''@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| root | 127.0.0.1 | |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
注意：有时用drop命令可能无法删除对应用户。比如，当数据库内的host等字段为大写及特殊Linux主机名时，删除用户就会遇到问题，例如:
mysql>drop user ' '@'MySQL';
ERROR1396 (HY000): Operation DROP USER failed for ' '@'mysql'
可使用DML语句，并采用delete命令删除来解决此问题，具体命令如下：
mysql>delete from mysql.user where user='' and host='MySQL';
QueryOK, 1 row affected (0.00 sec)
mysql>flush privileges;
QueryOK, 0 rows affected (0.00 sec)

mysql> drop database test; //
Query OK, 0 rows affected (0.11 sec)

http://www.cnblogs.com/clsn/p/8047028.html
（二）MySQL用户管理
a.定义用户：用户名+主机域，密码
b.定义权限：对不同的对象进行权限(角色)定义

命令:grant 权限 on 权限范围 to 用户 identified by '密码'
权限：对数据库的读、写等操作
（insert update、select、delete、drop、create等）
角色：数据库定义好的一组权限的定义
（all privileges、replication slave等）
权限范围：
全库级别： *.*
单库级别：crmn.*
单表级别：crmn.t1
用户：
'crmn'@'localhost' 本地
'crmn'@'192.168.66.149'
'crmn'@'192.168.66.%'
'crmn'@'192.168.66.14%'

例：按照要求创建用户,用户只能通过10.0.0.0/24网段访问，用户名为crmn,密码为123.
这个用户只能对crmn数据库下的对象进行增insert create、改update 、查select；
mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| root | 127.0.0.1 | |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant select,create,insert,update on crmn.* to crmn@'10.0.0.%' identified by '123'; //授权、创建用户
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for crmn@'10.0.0.%'; //查看权限
+------------------------------------------------------------------------------------------------------------+
| Grants for crmn@10.0.0.% |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'crmn'@'10.0.0.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, CREATE ON `crmn`.* TO 'crmn'@'10.0.0.%' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| root | 127.0.0.1 | |
| crmn | 10.0.0.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | //查看
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

1.创建用户:
CREATE USER '用户'@'主机' IDENTIFIED BY '密码';
示例：create user 'crmn'@'localhost' identified by 'crmn123';
注意这个样创建的用户只有连接权限
企业里创建用户一般是授权一个内网网段登录，最常见的网段写法有两种。
方法1：172.16.1.%（%为通配符，匹配所有内容）。
方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24，是个小遗憾。

mysql> create user web@'172.16.1.%' identified by 'web123';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for web@localhost
-> ;
ERROR 1141 (42000): There is no such grant defined for user 'web' on host 'localhost'
mysql> show grants for web@'172.16.1.%';
+-------------------------------------------------------------------------------------------------------------+
| Grants for web@172.16.1.% |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'web'@'172.16.1.%' IDENTIFIED BY PASSWORD '*67138D0908E294A380CA501A1F1A48898426B13B' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+------------+-------------------------------------------+
| user | host | password |
+------+------------+-------------------------------------------+
| root | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| root | 127.0.0.1 | |
| crmn | 10.0.0.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| web | 172.16.1.% | *67138D0908E294A380CA501A1F1A48898426B13B | //
+------+------------+-------------------------------------------+
4 rows in set (0.00 sec)

2.用户删除:
mysql> drop user web@'172.16.1.%';
Query OK, 0 rows affected (0.01 sec)

mysql> delete from mysql.user where user='crmn' and host='10.0.0.%';//法二
Query OK, 1 row affected (0.00 sec)

mysql> create user crmn@'localhost' identified by 'crmn123';//创建用户
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to crmn@'localhost';//授权所有权限给crmn用户
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for crmn@'localhost';//查看crmn用户的权限
+----------------------------------------------------------------------------------------------------------------------+
| Grants for crmn@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'crmn'@'localhost' IDENTIFIED BY PASSWORD '*BB4022AC0109472972DD4B8566CBD2C5CB23CD96' |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//法二：创建用户的同时授权
mysql> grant all on *.* to crmn@'172.16.1.%' identified by 'crmn123';//
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for crmn@'172.16.1.%'
-> ;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for crmn@172.16.1.% |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'crmn'@'172.16.1.%' IDENTIFIED BY PASSWORD '*BB4022AC0109472972DD4B8566CBD2C5CB23CD96' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> grant select,create,insert,update on crmn.* to crmn@'172.16.1.%';//修改权限
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for crmn@'172.16.1.%';//显示权限
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for crmn@172.16.1.% |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'crmn'@'172.16.1.%' IDENTIFIED BY PASSWORD '*BB4022AC0109472972DD4B8566CBD2C5CB23CD96' |
| GRANT SELECT, INSERT, UPDATE, CREATE ON `crmn`.* TO 'crmn'@'172.16.1.%' |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

//回收某个权限
mysql> show grants for crmn@'172.16.1.%';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for crmn@172.16.1.% |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'crmn'@'172.16.1.%' IDENTIFIED BY PASSWORD '*BB4022AC0109472972DD4B8566CBD2C5CB23CD96' |
| GRANT SELECT, INSERT, UPDATE, CREATE ON `crmn`.* TO 'crmn'@'172.16.1.%' |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke insert on crmn.* from crmn@'172.16.1.%';//回收insert权限
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for crmn@'172.16.1.%';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for crmn@172.16.1.% |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'crmn'@'172.16.1.%' IDENTIFIED BY PASSWORD '*BB4022AC0109472972DD4B8566CBD2C5CB23CD96' |
| GRANT SELECT, UPDATE, CREATE ON `crmn`.* TO 'crmn'@'172.16.1.%' |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
可以授权的用户权限:
INSERT,SELECT, 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 TABLESPACE

博客授权收回示例:
mysql> grant select,insert,update,delete,create,drop on blog.* to blog@'172.16.1.%' identified by 'blog123';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for blog@'172.16.1.%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for blog@172.16.1.% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'172.16.1.%' IDENTIFIED BY PASSWORD '*D6D67F3D1547519E29B7193DD019E55571BD09CA' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `blog`.* TO 'blog'@'172.16.1.%' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke create,drop on blog.* from blog@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for blog@'172.16.1.%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for blog@172.16.1.% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'172.16.1.%' IDENTIFIED BY PASSWORD '*D6D67F3D1547519E29B7193DD019E55571BD09CA' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'172.16.1.%' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

（三）MySQL客户端工具
mysql命令客户端：
a.用于数据库连接管理
b.将用户SQL 语句发送到服务器

mysqladmin命令：命令行管理工具
mysqldump命令：备份数据库和表的内容

mysql命令说明:
a.用于连接数据库
b.用于管理数据库通过下列方式进行管理

命令接口自带命令:
DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言

mysqladmin命令说明：
mysqladmin -u用户 -p密码 ping “强制回应 (Ping)”服务器。
mysqladmin -u用户 -p密码 shutdown 关闭服务器。
mysqladmin -u用户 -p密码 create databasename 创建数据库。
mysqladmin -u用户 -p密码drop databasename 删除数据库
mysqladmin -u用户 -p密码 version 显示服务器和版本信息
mysqladmin -u用户 -p密码 status 显示或重置服务器状态变量
mysqladmin -u用户 -p密码 password 设置口令
mysqladmin -u用户 -p密码 flush-privileges 重新刷新授权表。
mysqladmin -u用户 -p密码 flush-logs 刷新日志文件和高速缓存。
以上信息通过mysqladmin --help 获得

mysqldump简单说明：
mysqldump是一款数据库备份工具。
命令帮助及基本语法：
[root@db02 ~]# mysqldump --help
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
　　情参照mysqldump --help

mysql> show databases like "%s%"; //查看数据库--模糊匹配
+--------------------+
| Database (%s%) |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> ? show databases; //查看你相关的帮助
Name: 'SHOW DATABASES'
Description:
Syntax:
SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr]

//数据库操作:
mysql> create database haha; //1.创建一个数据库haha
Query OK, 1 row affected (0.00 sec)

mysql> show create database haha; //查询数据库定义信息
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| haha | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database crmn charset gbk; //创建数据库时定义字符编码
Query OK, 1 row affected (0.00 sec)

mysql> show create database crmn;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| crmn | CREATE DATABASE `crmn` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database crmn charset utf8; //存在的数据库修改字符编码
Query OK, 1 row affected (0.00 sec)

mysql> show create database crmn;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| crmn | CREATE DATABASE `crmn` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show character set; //查看支持的字符集和校对规则
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |

mysql> use crmn //切换数据库
Database changed
mysql> select database(); //查看当前所在数据库
+------------+
| database() |
+------------+
| crmn |
+------------+
1 row in set (0.01 sec)

mysql> select user(); //查看当前登陆的用户
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> show tables; //查看库里面的表
Empty set (0.00 sec)

2.DDL语句之管理表
表的属性：
a.字段、数据类型、索引
b.默认：字符集、引擎

表定义（列）：
a.表名、列名
b.列属性（数据类型、列约束）

mysql> create table test(id int);//创建表
Query OK, 0 rows affected (0.18 sec)

mysql> create table t1(idcard int,name char(30),sex char(4));
Query OK, 0 rows affected (0.02 sec)

mysql> create table t3(id int);
Query OK, 0 rows affected (0.01 sec)

ERROR:
No query specified
mysql> rename table t1 to haha;//修改表名字法一
Query OK, 0 rows affected (0.02 sec)

mysql> alter table people add addr char(40) NOT NULL;//修改表结构
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table people add major int(20) after addr;//指定添加专业列到addr列后面的位置
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table people add telnum int first;//第一列添加telnum字段到首行
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table people add idcard1 int first,add sex1 char(4) after name;//同时添加多个列定义
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table people drop sex;//删除表结构
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table people modify name char(20);//修改表定义
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table people change name people_name char(30);//修改列名
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

3.DCL数据库控制语言
[root@crmn ~]# netstat -lntup|grep 330
[root@crmn ~]# /etc/init.d/mysqld start
Starting MySQL... [确定]
[root@crmn ~]# netstat -lntup|grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3730/mysqld
[root@crmn ~]# mysql -uroot -poldboy123
......
mysql>
mysql> use crmn
Database changed
mysql> show tables;
+----------------+
| Tables_in_crmn |
+----------------+
| people |
| t3 |
| test |
+----------------+
3 rows in set (0.01 sec)

mysql> show grants for crmn@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for crmn@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'crmn'@'localhost' IDENTIFIED BY PASSWORD '*BB4022AC0109472972DD4B8566CBD2C5CB23CD96' |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> grant select,create,insert,update on ming.* to ming@'localhost' identified by 'ming123';//eg:创建用户的同时进行授权
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for ming@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for ming@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ming'@'localhost' IDENTIFIED BY PASSWORD '*9DEACE5C743A7ADC01E960D1564909B90BA441B6' |
| GRANT SELECT, INSERT, UPDATE, CREATE ON `ming`.* TO 'ming'@'localhost' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
(2)revoke：回收权限
mysql> revoke insert on ming.* from ming@localhost;//eg:回收权限insert
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for ming@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for ming@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ming'@'localhost' IDENTIFIED BY PASSWORD '*9DEACE5C743A7ADC01E960D1564909B90BA441B6' |
| GRANT SELECT, UPDATE, CREATE ON `ming`.* TO 'ming'@'localhost' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4.DML数据库操作语言:DML是针对数据行的操作
（1）insert语句：
mysql> use crmn //切换使用名为crmn的数据库
Database changed
mysql> show tables;
+----------------+
| Tables_in_crmn |
+----------------+
| people |
| t3 |
| test |
+----------------+
3 rows in set (0.00 sec)
mysql> create table crmn (id int,name varchar(20));//建表名为crmn，插入数据
Query OK, 0 rows affected (0.02 sec)

mysql> insert into crmn values(1,'crmn');//在表中插入第一行数据
Query OK, 1 row affected (0.02 sec)

mysql> select * from crmn;
+------+------+
| id | name |
+------+------+
| 1 | crmn |
+------+------+
1 row in set (0.00 sec)
mysql> insert into crmn values(2,'yongboy'),(3,'yonggirl');//插入两行数据
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from crmn;
+------+----------+
| id | name |
+------+----------+
| 1 | crmn |
| 2 | yongboy |
| 3 | yonggirl |
+------+----------+
3 rows in set (0.00 sec)
mysql> insert into crmn(name) values('xiaoming');//仅在name下插入一个名字
Query OK, 1 row affected (0.00 sec)

mysql> insert into `test1` values(1,'crmn'),(2,'caocao'),(3,'liubei'),(4,'sunquan');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test1;
+------+---------+
| id | name |
+------+---------+
| 1 | crmn |
| 2 | caocao |
| 3 | liubei |
| 4 | sunquan |
+------+---------+
4 rows in set (0.00 sec)
mysql> create table test2 like crmn;//使用insert复制表结构及内容，产生附表。
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test2 select * from crmn;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test2;//复制成功！
+------+----------+
| id | name |
+------+----------+
| 1 | crmn |
| 2 | yongboy |
| 3 | yonggirl |
| NULL | xiaoming |
+------+----------+
4 rows in set (0.00 sec)
mysql> select * from crmn;//
+------+----------+
| id | name |
+------+----------+
| 1 | crmn |
| 2 | yongboy |
| 3 | yonggirl |
| NULL | xiaoming |
+------+----------+
4 rows in set (0.00 sec)
（2）update更改数据库(一定要加上where条件)：
mysql> select * from test2;//更新表内容,将字段中的yonggirl改为haha
+------+----------+
| id | name |
+------+----------+
| 1 | crmn |
| 2 | yongboy |
| 3 | yonggirl |
| NULL | xiaoming |
+------+----------+
4 rows in set (0.00 sec)

mysql> update test2 set name='haha' where name='yonggirl';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

（3）delete删除表内容(一定要有where条件)：
mysql> delete from test2 where name='xiaoming';//删除xiaoming的记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+------+---------+
| id | name |
+------+---------+
| 1 | crmn |
| 2 | yongboy |
| 3 | haha |
+------+---------+
3 rows in set (0.00 sec)
//删除语句:
delete from test; #逻辑删除，一行一行删。
truncate table test; #物理删除，pages(block)，效率高。
（4）生产中的伪删除
eg:为表添加一个state列，TINYINT，字段类型，如果设置为UNSIGNED类型，只能存储从0到255的整数,不能用来储存负数。
mysql> desc test2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table test2 add state tinyint(2) not null default 1;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test2;//查看当前的state状态
+------+---------+-------+
| id | name | state |
+------+---------+-------+
| 1 | crmn | 1 |
| 2 | yongboy | 1 |
| 3 | haha | 1 |
+------+---------+-------+
3 rows in set (0.00 sec)
mysql> update test2 set state=0 where name='crmn';//更新数据，将crmn记录的state改为0
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test2;
+------+---------+-------+
| id | name | state |
+------+---------+-------+
| 1 | crmn | 0 |
| 2 | yongboy | 1 |
| 3 | haha | 1 |
+------+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from test2 where state=1;// 查询的时候，使用where条件只显示 state=1 的记录，效果与删除类似。
+------+---------+-------+
| id | name | state |
+------+---------+-------+
| 2 | yongboy | 1 |
| 3 | haha | 1 |
+------+---------+-------+
2 rows in set (0.00 sec)

（5）防止不加条件误删【安全】
备份数据备用：
[root@crmn ~]# mysqldump -B --compact crmn >/opt/mysqldata_bak.sql -poldboy123
[root@crmn ~]# cat /opt/mysqldata_bak.sql

mysql> use crmn
Database changed
mysql> update test2 set name='222';//使用update与delete命令的时候不加where 条件不会执行。
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

mysql> delete from crmn.test2;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql>
[root@crmn ~]# echo "alias mysql='mysql -U'" >>/etc/profile//做成别名防止老大和DBA误操作
[root@crmn ~]# . /etc/profile
[root@crmn ~]# tail -1 /etc/profile
alias mysql='mysql -U'
结论：
在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行

5.DQL数据查询语言标准语法
select命令语法：
select <字段1，字段2，...> from < 表名 > where < 表达式 > and < 表达式 >。
　　其中，select、from、where是不能随便改的，是关键字，支持大小写。
select <字段1，字段2，...> from < 表名 > where < 表达式 >
mysql> select user,host,password from mysql.user;//查看用户的连接信息
+------+------------+-------------------------------------------+
| user | host | password |
+------+------------+-------------------------------------------+
| root | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| root | 127.0.0.1 | |
| crmn | localhost | *BB4022AC0109472972DD4B8566CBD2C5CB23CD96 |
| crmn | 172.16.1.% | *BB4022AC0109472972DD4B8566CBD2C5CB23CD96 |
| blog | 172.16.1.% | *D6D67F3D1547519E29B7193DD019E55571BD09CA |
| ming | localhost | *9DEACE5C743A7ADC01E960D1564909B90BA441B6 |
+------+------------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> select * from crmn.test2;//查看test2表中的所有信息
+------+---------+-------+
| id | name | state |
+------+---------+-------+
| 1 | crmn | 0 |
| 2 | yongboy | 1 |
| 3 | haha | 1 |
+------+---------+-------+
3 rows in set (0.00 sec)
mysql> select id,name from crmn.test2;//查看test表中的id和name
+------+---------+
| id | name |
+------+---------+
| 1 | crmn |
| 2 | yongboy |
| 3 | haha |
+------+---------+
3 rows in set (0.00 sec)
mysql> select id,name from test2 where id=2;//查看id等于2 的信息
+------+---------+
| id | name |
+------+---------+
| 2 | yongboy |
+------+---------+
1 row in set (0.00 sec)
mysql> select id,name from test2 where name='crmn';//查看名字是crmn的记录
+------+------+
| id | name |
+------+------+
| 1 | crmn |
+------+------+
1 row in set (0.00 sec)
mysql> select id,name from test2 where id>2;//查看id大于2的记录
+------+------+
| id | name |
+------+------+
| 3 | haha |
+------+------+
1 row in set (0.00 sec)
mysql> select id,name from test2 where id>2 and id<4;//查看id大于2并且小于4的记录
+------+------+
| id | name |
+------+------+
| 3 | haha |
+------+------+
1 row in set (0.00 sec)
mysql> select id,name from test2 where id>2 or id<4;//查看id大于2或者小于4的记录
+------+---------+
| id | name |
+------+---------+
| 1 | crmn |
| 2 | yongboy |
| 3 | haha |
+------+---------+
3 rows in set (0.00 sec)
mysql> select id,name from test2 order by id asc;//提取记录排序
+------+---------+
| id | name |
+------+---------+
| 1 | crmn |
| 2 | yongboy |
| 3 | haha |
+------+---------+
3 rows in set (0.00 sec)
mysql> select id,name from test2 order by id desc;//
+------+---------+
| id | name |
+------+---------+
| 3 | haha |
| 2 | yongboy |
| 1 | crmn |
+------+---------+
3 rows in set (0.00 sec)
mysql> select id,name from test2 limit 1,3;//显示排错第一行后的三行(需要与排序配合使用)
+------+---------+
| id | name |
+------+---------+
| 2 | yongboy |
| 3 | haha |
+------+---------+
2 rows in set (0.00 sec)

6.字符集说明:
(1)常用的字符集:
MySQL数据库的字符集：字符集（CHARACTER）、校对规则（COLLATION）
MySQL中常见的字符集：UTF8、LATIN1、GBK
常见校对规则：ci：大小写不敏感、cs或bin：大小写敏感
我们可以使用以下命令查看：show charset; 、 show collation；

(2)字符集设置
a.系统字符集说明:
[root@crmn ~]# cat /etc/sysconfig/i18n
LANG="zh_CN.UTF-8"
[root@crmn ~]# echo $LANG
zh_CN.UTF-8
b.客户端字符集说明:
方法1：在编译安装时候就指定如下服务器端字符集。

cmake .
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
方法2：在my.cnf文件中添加上字符参数

[mysqld]
character-set-server=utf8

mysql> create database crmn_utf8 default character set utf8 default collate=utf8_general_ci;//建立一个名为crmn_utf8的utf8字符集数据库
Query OK, 1 row affected (0.00 sec)

mysql> show create database crmn_utf8;
+-----------+--------------------------------------------------------------------+
| Database | Create Database |
+-----------+--------------------------------------------------------------------+
| crmn_utf8 | CREATE DATABASE `crmn_utf8` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database crmn_gbk default character set gbk collate=gbk_chinese_ci;//建立一个名为crmn_gbk的gbk字符集数据库
Query OK, 1 row affected (0.00 sec)

mysql> show create database crmn_gbk;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| crmn_gbk | CREATE DATABASE `crmn_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

获取帮助并查询:
mysql> help create database;
mysql> show character set;

//MySQL客户端级别（连接及返回结果）
方法1：临时生效单条命令法。
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
　　
方法2：通过修改my.cnf实现修改mysql客户端的字符集，配置方法如下
[client]
default-character-set=utf8
　　
程序代码级别：生产环境更改数据库（含数据）字符集的方法
alter database clsn CHARACTER SET utf8 collate utf8_general_ci;
alter table t1 CHARACTER SET latin1;

（四）数据类型说明
四种主要类别：数值类型、字符类型、时间类型、二进制类型
数据类型的ABC要素：Appropriate（适当） Brief（简洁） Complete（完整）

posted @ 2017-12-19 17:50 bkycrmn 阅读(176) 评论(0) 收藏举报

刷新页面返回顶部

1.2 MySQL用户管理及SQL语句详解

公告