MySQL用户管理及SQL语句详解

1.1 MySQL用户管理

1.1.1 用户的定义

　　用户名+主机域

mysql> select user,host,password from mysql.user;

+--------+------------+-------------------------------------------+

| user   | host       | password                                  |

+--------+------------+-------------------------------------------+

| root   | localhost  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| root   | 127.0.0.1  |                                           |

| znix   | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| clsn   | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| root   | 10.0.0.1   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

+--------+------------+-------------------------------------------+

6 rows in set

1.1.2 用户的作用

　　　　1、用户登录

　　　　2、用于管理数据库及数据

1.1.3 连接数据库

　　定义用户：用户名+主机域，密码

　　定义权限：对不同的对象进行权限(角色)定义

命令:

  grant 权限 on 权限范围  to 用户  identified  by '密码'

权限

        对数据库的读、写等操作

    （insert update、select、delete、drop、create等）

角色

    数据库定义好的一组权限的定义

    （all privileges、replication slave等）

权限范围

    全库级别： *.*

    单库级别：clsn.*

    单表级别：clsn.t1

用户

    'clsn'@'localhost'  本地

    'clsn'@'192.168.66.149'

    'clsn'@'192.168.66.%'

    'clsn'@'192.168.66.14%'

1.1.4 【练习题】按照要求创建用户

用户只能通过10.0.0.0/24网段访问，用户名为clsn 密码为123

这个用户只能对clsn数据库下的对象进行增insert create、改update 、查select；

创建命令：

    grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '';

查看用户权限

mysql>  show grants for clsn@'172.16.1.%'\G

*************************** 1. row ***************************

Grants for clsn@172.16.1.%: GRANT USAGE ON *.* TO 'clsn'@'172.16.1.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

1 row in set (0.00 sec)

查看当前存在的用户:

select user,host from mysql.user;

创建用户语法

CREATE USER '用户'@'主机' IDENTIFIED BY '密码';

示例：

create user 'clsn'@'localhost' identified by 'clsn123';

注意这个样创建的用户只有连接权限

企业里创建用户一般是授权一个内网网段登录，最常见的网段写法有两种。

方法1：172.16.1.%（%为通配符，匹配所有内容）。

方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24，是个小遗憾。

标准的建用户方法：

create user 'web'@'172.16.1.%' identified by 'web123';

查看用户对应的权限

show grants for oldboy@localhost\G

1.1.5 用户删除

删除用户语法：

drop user 'user'@'主机域'

【练习】用户优化：只保留

| root | 127.0.0.1 |

| root | localhost |

特殊的删除方法:(慎用，尽量不要直接去修改表)

mysql> delete from mysql.user where  user='clsn' and host='localhost';

Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;

1.1.6 用户授权

给用户授权

# 创建用户

create user 'clsn'@'localhost' identified by 'clsn123';

# 查看用户

select user,host from mysql.user;

# 授权所有权限给clsn用户

GRANT ALL ON *.* TO 'clsn'@'localhost';

# 查看clsn用户的权限

SHOW GRANTS FOR 'clsn'@'localhost'\G

创建用户的同时授权

grant all on *.* to clsn@'172.16.1.%' identified by 'clsn123';

# 刷新权限

flush privileges; #<==可以不用。

创建用户然后授权

create user 'clsn'@'localhost' identified by 'clsn123';

GRANT ALL ON *.* TO 'clsn'@'localhost';

授权和root一样的权限

grant all on *.* to system@'localhost' identified by 'clsn123' with grant option;

授权给用户select,create,insert,update 权限

grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '';

回收权限

REVOKE INSERT ON *.* FROM clsn@localhost;

可以授权的用户权限

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

【示例】博客授权收回示例

grant select,insert,update,delete,create,drop on blog.* to 'blog'@'172.16.1.%' identified by 'blog123';

revoke create,drop on blog.* from 'blog'@'172.16.1.%';

授权博客类的最多权限：select,insert,update,delete

1.2 MySQL 客户端工具

1.2.1 MysQL客户端命令介绍

　　mysql命令客户端

　　　　用于数据库连接管理

　　　　将用户SQL 语句发送到服务器

　　mysqladmin命令：命令行管理工具

　　mysqldump命令：备份数据库和表的内容

1.2.2 mysql命令说明

　　用于连接数据库

　　用于管理数据库通过下列方式进行管理

命令接口自带命令

　　DDL：数据定义语言

　　DCL：数据控制语言

　　DML：数据操作语言

　　mysql命令接口自带命令说明

命令	命令说明
\h 或 help 或 ?	获取帮助
\G	格式化输出（行转列）
\T 或 tee	记录操作日志 tee /tmp/mysql.log
\c 或 CTRL+c	退出mysql
\s 或 status	查看数据库状态信息
\. 或 source	mysql> source /tmp/world.sql
\!	使用shell中的命令 mysql> \! cat /etc/redhat-release CentOS release 6.9 (Final)
\u 或use	use world show databases 看当前所有数据库的名字 show tables 查看当前use到的数据库所有的表 show tables from world 查看目标数据库下的表
快捷键	上下翻页、TAB键、ctrl +C 、ctrl +L

1.2.2.1 mysql中help命令的使用

　　在mysql命令行中输入mysql或 ?都可以查看帮助

mysql> help

　　使用 help contents查看完整的sql类别列表帮助

mysql> help contents

　　有关特定 SQL 类别或语句的帮助

mysql> help Account Management

　　查看 grant 的帮助

mysql> help GRANT

　　有关与状态相关的 SQL 语句的帮助

mysql> help status

1.2.2.2 source命令的使用

在 mysql 中处理输入文件：

如果这些文件包含 SQL 语句，则称为“脚本文件”或“批处理文件”。

使用 SOURCE 命令：

mysql> SOURCE /data/mysql/world.sql

　　或者使用非交互式：(尽量避免使用mysql 导入数据，会产生大量的无用日志)

mysql</data/mysql/world.sql

1.2.3 mysqladmin命令说明

基本语法

mysqladmin -u<name> -p<password> commands

　　命令说明图表

功能选项	说明
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 获得

1.2.4 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

1.3 SQL语句入门

1.3.1 DDL语句(数据定义语言)

定义范围：

库：名字、特性

表：表名字、列

1.3.1.1 数据库查看

查看数据库--查看全部

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| clsn               |

+--------------------+

查看数据库--模糊匹配

mysql> show databases like "%s%";

+--------------------+

| Database (%s%)     |

+--------------------+

| information_schema |

| clsn               |

| mysql              |

| performance_schema |

+--------------------+

4 rows in set (0.00 sec)

查看你相关的帮助

mysql> ? show databases;

Name: 'SHOW DATABASES'

Description:

Syntax:

SHOW {DATABASES | SCHEMAS}

    [LIKE 'pattern' | WHERE expr]

1.3.1.2 数据库操作

　　创建一个数据库

mysql> create database haha;

通过show 命令能够查看创建的数据库的格式

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 clsn charset utf8 ;

mysql> show create database clsn;     查询数据库定义信息。

存在的数据库修改字符编码：

mysql> alter database clsn charset gbk;

修改数据库编码格式示例

mysql> alter database haha charset gbk

    -> ;

Query OK, 1 row affected (0.00 sec)

mysql> show create database haha;

+----------+--------------------------------------------------------------+

| Database | Create Database                                              |

+----------+--------------------------------------------------------------+

| haha     | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET gbk */ |

+----------+--------------------------------------------------------------+

1 row in set (0.00 sec)

　　标准修改系统

ALTER DATABASE [db_name] CHARACTER SET  charset_name COLLATE collation_name;

ALTER DATABASE clsn CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查看支持的字符集和校对规则.

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> drop database haha;

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

切库

mysql> use  clsn;

Database changed

查看当前所在数据库

mysql> select  database();

+------------+

| database() |

+------------+

| clsn       |

+------------+

1 row in set (0.00 sec)

查看当前登陆的用户

mysql> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

查看库里面的表

mysql> show tables;

+----------------+

| Tables_in_clsn |

+----------------+

| t1             |

| t2             |

| test           |

+----------------+

3 rows in set (0.00 sec)

1.3.1.3 DDL语句之管理表

表的属性：

字段、数据类型、索引

默认：字符集、引擎

表定义（列）：

表名、列名

列属性（数据类型、列约束）

创建表

create table t3 (id int);

创建更多的表；

create table test(id int);

create table t1(idcard int ,name char(30),sex char(4));

查看表结构

mysql> desc t2

    -> ;

+--------+----------+------+-----+---------+-------+

| Field  | Type     | Null | Key | Default | Extra |

+--------+----------+------+-----+---------+-------+

| idcard | int(11)  | YES  |     | NULL    |       |

| name   | char(30) | YES  |     | NULL    |       |

| sex    | char(4)  | YES  |     | NULL    |       |

| addr   | char(4)  | NO   |     | NULL    |       |

+--------+----------+------+-----+---------+-------+

4 rows in set (0.00 sec)

查看建表语句

mysql> show create table t2\G

*************************** 1. row ***************************

       Table: t2

Create Table: CREATE TABLE `t2` (

  `idcard` int(11) DEFAULT NULL,

  `name` char(30) DEFAULT NULL,

  `sex` char(4) DEFAULT NULL,

  `addr` char(4) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

1.3.1.4 修改表的定义

修改表名字

mysql> rename table t3 to haha;

mysql> show tables ;

+----------------+

| Tables_in_clsn |

+----------------+

| haha           |

修改表名字，方法二。

mysql> alter table haha rename to people;

mysql> show tables;

+----------------+

| Tables_in_clsn |

+----------------+

| people         |

修改表结构

mysql> alter table people  add addr char(40) NOT NULL;

mysql> desc people;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id    | int(11)  | YES  |     | NULL    |       |

| addr  | char(40) | NO   |     | NULL    |       |

+-------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

指定添加年龄列到name列后面的位置，示例如下：

mysql> alter table people add age int(4) after name;

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc people;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id    | int(11)  | YES  |     | NULL    |       |

| addr  | char(40) | NO   |     | NULL    |       |

| name  | int(40)  | YES  |     | NULL    |       |

| age   | int(4)   | YES  |     | NULL    |       |

+-------+----------+------+-----+---------+-------+

4 rows in set (0.00 sec)

通过下面的命令在第一列添加qq字段。

mysql> alter table people add telnum  int  first;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc people;

+--------+----------+------+-----+---------+-------+

| Field  | Type     | Null | Key | Default | Extra |

+--------+----------+------+-----+---------+-------+

| telnum | int(11)  | YES  |     | NULL    |       |

| id     | int(11)  | YES  |     | NULL    |       |

| addr   | char(40) | NO   |     | NULL    |       |

| name   | int(40)  | YES  |     | NULL    |       |

| age    | int(4)   | YES  |     | NULL    |       |

+--------+----------+------+-----+---------+-------+

5 rows in set (0.00 sec)

同时添加多个列定义：

mysql> alter table people add id1 int first ,add sex char(4) after name ;

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc people;

+--------+----------+------+-----+---------+-------+

| Field  | Type     | Null | Key | Default | Extra |

+--------+----------+------+-----+---------+-------+

| id1    | int(11)  | YES  |     | NULL    |       |

| telnum | int(11)  | YES  |     | NULL    |       |

| id     | int(11)  | YES  |     | NULL    |       |

| addr   | char(40) | NO   |     | NULL    |       |

| name   | int(40)  | YES  |     | NULL    |       |

| sex    | char(4)  | YES  |     | NULL    |       |

| age    | int(4)   | YES  |     | NULL    |       |

+--------+----------+------+-----+---------+-------+

7 rows in set (0.00 sec)

删除表结构：

mysql> alter table people  drop  sex;

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  people;

+--------+----------+------+-----+---------+-------+

| Field  | Type     | Null | Key | Default | Extra |

+--------+----------+------+-----+---------+-------+

| id1    | int(11)  | YES  |     | NULL    |       |

| telnum | int(11)  | YES  |     | NULL    |       |

| id     | int(11)  | YES  |     | NULL    |       |

| addr   | char(40) | NO   |     | NULL    |       |

| name   | int(40)  | YES  |     | NULL    |       |

| age    | int(4)   | YES  |     | NULL    |       |

+--------+----------+------+-----+---------+-------+

6 rows in set (0.00 sec)

修改表定义

mysql> alter table people modify name char(20);

Query OK, 0 rows affected (0.11 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  people;

+--------+----------+------+-----+---------+-------+

| Field  | Type     | Null | Key | Default | Extra |

+--------+----------+------+-----+---------+-------+

| id1    | int(11)  | YES  |     | NULL    |       |

| telnum | int(11)  | YES  |     | NULL    |       |

| id     | int(11)  | YES  |     | NULL    |       |

| addr   | char(40) | NO   |     | NULL    |       |

| name   | char(20) | YES  |     | NULL    |       |

| age    | int(4)   | YES  |     | NULL    |       |

+--------+----------+------+-----+---------+-------+

6 rows in set (0.00 sec)

修改列名：

mysql> alter table people change name people_name char(30) ;

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  people;

+-------------+----------+------+-----+---------+-------+

| Field       | Type     | Null | Key | Default | Extra |

+-------------+----------+------+-----+---------+-------+

| id1         | int(11)  | YES  |     | NULL    |       |

| telnum      | int(11)  | YES  |     | NULL    |       |

| id          | int(11)  | YES  |     | NULL    |       |

| addr        | char(40) | NO   |     | NULL    |       |

| people_name | char(30) | YES  |     | NULL    |       |

| age         | int(4)   | YES  |     | NULL    |       |

+-------------+----------+------+-----+---------+-------+

6 rows in set (0.00 sec)

1.3.1.5 DDL语句小结

create database  xxx   charset   # 创建数据库 设置字符集

create  table   xxx  (id int,xxxxxx)  # 创建表

drop   table    # 删除表

drop   database  # 参数数据库

alter table   add/drop/modify/change   # 表内容修改

create user   # 创建用户

drop  user  #删除用户

1.3.2 DCL数据库控制语言

grant：用户授权

GRANT ALL ON *.* TO 'clsn'@'localhost';

SHOW GRANTS FOR 'clsn'@'localhost'\G

创建用户的同时进行授权

grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '';

revoke：回收权限

REVOKE INSERT ON *.* FROM clsn@localhost;

收回权限示例：

revoke drop,delete on *.* from sys@localhost;

1.3.3 DML数据库操作语言

　　　　DML是针对数据行的操作

1.3.3.1 insert 语句

　　 insert 语法结构

insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]

创建表，插入数据

mysql> use clsn

mysql> create table clsn (id int,name varchar(20));

Query OK, 0 rows affected (0.06 sec)

表的类型

mysql> desc clsn;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(11)     | YES  |     | NULL    |       |

| name  | varchar(20) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

在表中插入第一行数据

mysql> insert into clsn values(1,'clsn');

Query OK, 1 row affected (0.00 sec)

mysql> select * from clsn;

+------+------+

| id   | name |

+------+------+

|    1 | clsn |

+------+------+

1 row in set (0.00 sec)

插入两行数据

mysql> insert into clsn values(2,'yougboy'),(3,'youggilr');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from clsn;

+------+----------+

| id   | name     |

+------+----------+

|    1 | clsn     |

|    2 | yougboy  |

|    3 | youggilr |

+------+----------+

3 rows in set (0.00 sec)

仅在name下插入一个名字

mysql> insert into clsn(name) values('xiaoming');

Query OK, 1 row affected (0.01 sec)

mysql> select * from clsn;

+------+----------+

| id   | name     |

+------+----------+

|    1 | clsn     |

|    2 | yougboy  |

|    3 | youggilr |

| NULL | xiaoming |

+------+----------+

4 rows in set (0.00 sec)

一次插入多行数据

mysql> create table test1(id int ,name varchar(20));

INSERT INTO `test1` VALUES (1,'clsn'),(2,'znix'),(3,'inca'),(4,'zuma'),(5,'kaka');

mysql> select * from test1;

+------+------+

| id   | name |

+------+------+

|    1 | clsn |

|    2 | znix |

|    3 | inca |

|    4 | zuma |

|    5 | kaka |

+------+------+

5 rows in set (0.00 sec)

　　使用 insert 复制表结构及内容，产生附表。

mysql> create table test2 like clsn;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into test2 select * from clsn;

Query OK, 4 rows affected (0.01 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> select  * from test2;

+------+----------+

| id   | name     |

+------+----------+

|    1 | clsn     |

|    2 | yougboy  |

|    3 | youggilr |

| NULL | xiaoming |

+------+----------+

4 rows in set (0.00 sec)

1.3.3.2 update更改数据库(一定要加上where条件)

　　更新表内容

mysql> select * from  test2;

+------+----------+

| id   | name     |

+------+----------+

|    1 | clsn     |

|    2 | yougboy  |

|    3 | youggilr |

| NULL | xiaoming |

+------+----------+

4 rows in set (0.00 sec)

将字段中的youggirl改为haha

mysql> update test2 set name='haha' where name='youggilr';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

更改后

mysql> select * from  test2;

+------+----------+

| id   | name     |

+------+----------+

|    1 | clsn     |

|    2 | yougboy  |

|    3 | haha     |

| NULL | xiaoming |

+------+----------+

4 rows in set (0.00 sec)

1.3.3.3 delete删除表内容(一定要有where条件)

　　删除语句

delete from test;     #逻辑删除，一行一行删。

truncate table test;  #物理删除，pages(block)，效率高。

　　删除之前

mysql> select * from  test2;

+------+----------+

| id   | name     |

+------+----------+

|    1 | clsn     |

|    2 | yougboy  |

|    3 | haha     |

| NULL | xiaoming |

+------+----------+

4 rows in set (0.00 sec)

删除xiaoming的记录

mysql> delete from test2 where name='xiaoming';

Query OK, 1 row affected (0.04 sec)

删除之后

mysql> select * from  test2;

+------+---------+

| id   | name    |

+------+---------+

|    1 | clsn    |

|    2 | yougboy |

|    3 | haha    |

+------+---------+

3 rows in set (0.00 sec)

1.3.3.4 生产中的伪删除

　　为表添加一个state列

　　TINYINT ，字段类型，如果设置为UNSIGNED类型，只能存储从0到255的整数,不能用来储存负数。

mysql> alter table test2  add  state tinyint(2) not null default 1;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test2;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(11)     | YES  |     | NULL    |       |

| name  | varchar(20) | YES  |     | NULL    |       |

| state | tinyint(2)  | NO   |     | 1       |       |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> update test set state=1;

查看当前的state状态

mysql> select   * from  test2;

+------+---------+-------+

| id   | name    | state |

+------+---------+-------+

|    1 | clsn    |     1 |

|    2 | yougboy |     1 |

|    3 | haha    |     1 |

+------+---------+-------+

3 rows in set (0.00 sec)

更新数据，将clsn记录的state改为0

mysql> update test2 set state=0 where name='clsn';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select   * from  test2;

+------+---------+-------+

| id   | name    | state |

+------+---------+-------+

|    1 | clsn    |     0 |

|    2 | yougboy |     1 |

|    3 | haha    |     1 |

+------+---------+-------+

3 rows in set (0.00 sec)

查询的时候，使用 where 条件只显示 state=1 的记录，效果与删除类似。

mysql> select * from test2 where state=1;

+------+---------+-------+

| id   | name    | state |

+------+---------+-------+

|    2 | yougboy |     1 |

|    3 | haha    |     1 |

+------+---------+-------+

2 rows in set (0.00 sec)

1.3.3.5 防止不加条件误删【安全】

备份数据备用

mysqldump以SQL语句形式将数据导出来。

mysqldump -B --compact clsn >/opt/bak.sql

mysql -U 参数实践

-U, --safe-updates  Only allow UPDATE and DELETE that uses keys.

使用update与delete命令的时候不加where 条件不会执行。

mysql> update test2 set name='xixi';

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命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行。

1.3.4 DQL数据查询语言标准语法

1.3.4.1 语法说明

select命令语法：

select <字段1，字段2，...> from < 表名 > where < 表达式 > and < 表达式 >。

　　其中，select、from、where是不能随便改的，是关键字，支持大小写。

select <字段1，字段2，...> from < 表名 > where < 表达式 >

1.3.4.2 查看操作

查看用户的连接信息

mysql> select user,host,password from mysql.user;

+------+------------+-------------------------------------------+

| user | host       | password                                  |

+------+------------+-------------------------------------------+

| root | localhost  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| root | 127.0.0.1  |                                           |

| znix | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| clsn | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| root | 10.0.0.1   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

+------+------------+-------------------------------------------+

5 rows in set (0.00 sec)

查看test2表中的所有信息

mysql> select * from clsn.test2;

+------+---------+-------+

| id   | name    | state |

+------+---------+-------+

|    1 | clsn    |     0 |

|    2 | yougboy |     1 |

|    3 | haha    |     1 |

+------+---------+-------+

3 rows in set (0.00 sec)

查看test表中的id和name

mysql> select id,name from clsn.test2;

+------+---------+

| id   | name    |

+------+---------+

|    1 | clsn    |

|    2 | yougboy |

|    3 | haha    |

+------+---------+

3 rows in set (0.00 sec)

查看id等于2 的信息

mysql> select id,name from test2 where id=2;

+------+---------+

| id   | name    |

+------+---------+

|    2 | yougboy |

+------+---------+

1 row in set (0.00 sec)

查看名字是clsn的记录

mysql> select id,name from test2 where name='clsn';

+------+------+

| id   | name |

+------+------+

|    1 | clsn |

+------+------+

1 row in set (0.00 sec)

查看id大于2的记录

mysql> select id,name from test2 where id>2;

+------+------+

| id   | name |

+------+------+

|    3 | haha |

+------+------+

1 row in set (0.00 sec)

查看id大于2 并且小于4的记录

mysql> select id,name from test2 where id>2 and id<4;

+------+------+

| id   | name |

+------+------+

|    3 | haha |

+------+------+

1 row in set (0.00 sec)

查看id大于2或者小于4的记录

mysql> select id,name from test2 where id>2 or id<4;

+------+---------+

| id   | name    |

+------+---------+

|    1 | clsn    |

|    2 | yougboy |

|    3 | haha    |

+------+---------+

3 rows in set (0.00 sec)

提取记录排序

mysql> select id,name from test2 order by id asc;

+------+---------+

| id   | name    |

+------+---------+

|    1 | clsn    |

|    2 | yougboy |

|    3 | haha    |

+------+---------+

3 rows in set (0.00 sec)

mysql> select id,name from test2 order by id desc;

+------+---------+

| id   | name    |

+------+---------+

|    3 | haha    |

|    2 | yougboy |

|    1 | clsn    |

+------+---------+

3 rows in set (0.00 sec)

显示排错第一行后的三行(需要与排序配合使用)

mysql> select id,name from test2 limit 1,3;

+------+---------+

| id   | name    |

+------+---------+

|    2 | yougboy |

|    3 | haha    |

+------+---------+

2 rows in set (0.00 sec)

1.3.5 字符集说明

1.3.5.1 常用的字符集

　　MySQL数据库的字符集：字符集（CHARACTER）、校对规则（COLLATION）

　　MySQL中常见的字符集：UTF8、LATIN1、GBK

　　常见校对规则：ci：大小写不敏感、cs或bin：大小写敏感

　　我们可以使用以下命令查看：show charset; 、 show collation；

1.3.5.2 字符集设置

系统字符集说明

[root@db02 ~]# cat  /etc/sysconfig/i18n

LANG="en_US.UTF-8"

SYSFONT="latarcyrheb-sun16"

[root@db02 ~]# echo $LANG

en_US.UTF-

客户端字符集说明

　　方法1：在编译安装时候就指定如下服务器端字符集。

cmake .

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_EXTRA_CHARSETS=all \

　　方法2：在my.cnf文件中添加上字符参数

[mysqld]

character-set-server=utf8

数据库中的库级别设置

CREATE DATABASE `clsn` /*!40100 DEFAULT CHARACTER SET utf8 */

create database clsn DEFAULT CHARACTER SET UTF8 DEFAULT COLLATE = utf8_general_ci;

　　获取帮助并查询

help create database;

show character set;

　　表级别（含字段级别）

CREATE TABLE `test` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

1.3.5.3 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;

1.3.6 常用的查询操作

查看当前所在的数据库

mysql> select database();

+------------+

| database() |

+------------+

| clsn       |

+------------+

1 row in set (0.00 sec)

查看当前的登陆用户

mysql> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

1.4 数据类型说明

1.4.1 ER模型

　　ER模型，全称为实体联系模型、实体关系模型或实体联系模式图（ERD）（英语：Entity-relationship model）由美籍华裔计算机科学家陈品山发明，是概念数据模型的高层描述所使用的数据模型或模式图。

　　ER模型常用于信息系统设计中；比如它们在概念结构设计阶段用来描述信息需求和／或要存储在数据库中的信息的类型。但是数据建模技术可以用来描述特定论域（就是感兴趣的区域）的任何本体（就是对使用的术语和它们的联系的概述和分类）。在基于数据库的信息系统设计的情况下，在后面的阶段（通常叫做逻辑设计），概念模型要映射到逻辑模型如关系模型上；它依次要在物理设计期间映射到物理模型上。注意，有时这两个阶段被一起称为“物理设计”。

　　实体联系模式图（ERD）有一些约定。本文的余下部分描述经典概念，并且主要与概念建模有关。有一些概念更加典型的在逻辑和物理数据库设计中采用，包括信息工程、IDEF1x（ICAM DEFinition Language）和空间建模。

1.4.2 数据类型介绍

　　四种主要类别：数值类型、字符类型、时间类型、二进制类型

MySQL用户管理及SQL语句详解

　　数据类型的 ABC 要素： Appropriate（适当） Brief（简洁） Complete（完整）

1.4.3 创建带有数据类型的表

　　例 1：列声明

CREATE TABLE people (

id         INT,

first_name CHAR(30),

last_name CHAR(30) );

　　例 2：不允许负值和未知值

CREATE TABLE people (

id         INT UNSIGNED NOT NULL,

first_name CHAR(30),

last_name CHAR(30) );

1.4.4 数值数据类型

　　使用数值数据类型时的注意事项：

数据类型所表示的值的范围
列值所需的空间量
列精度和范围（浮点数和定点数）

　　数值数据类型的类：

整数：整数
浮点数：小数
定点数：精确值数值
BIT：位字段值

类	类型	说明
整数	TINYINT	极小整数数据类型（0-255）
整数	SMALLINT	较小整数数据类型（-2^15 到2^15-1）
整数	MEDIUMINT	中型整数数据类型
整数	INT	常规（平均）大小的整数数据类型（-2^31 到2^31-1）
整数	BIGINT	较大整数数据类型（-2^63到2^63-1）
浮点数	FLOAT	小型单精度（四个字节）浮点数
浮点数	DOUBLE	常规双精度（八个字节）浮点数
定点数	DECIMAL	包含整数部分、小数部分或同时包括二者的精确值数值
BIT	BIT	位字段值

1.4.5 字符串数据类型

　　表示给定字符集中的一个字母数字字符序列，用于存储文本或二进制数据，几乎在每种编程语言中都有实现，支持字符集和整理。

　　属于以下其中一类

　　　　文本：真实的非结构化字符串数据类型

　　　　整数：结构化字符串类型

类	类型	说明
文本	CHAR	固定长度字符串，最多为255 个字符
文本	VARCHAR	可变长度字符串，最多为65,535 个字符
文本	TINYTEXT	可变长度字符串，最多为255 个字符
文本	TEXT	可变长度字符串，最多为65,535 个字符
文本	MEDIUMTEXT	可变长度字符串，最多为16,777,215 个字符
文本	LONGTEXT	可变长度字符串，最多为4,294,967,295 个字符
整数	ENUM	由一组固定的合法值组成的枚举
整数	SET	由一组固定的合法值组成的集

1.4.6 二进制字符串数据类型

　　字节序列：二进制位按八位分组

　　存储二进制值，例如：编译的计算机程序和应用程序、图像和声音文件

　　字符二进制数据类型的类：

　　　　二进制：固定长度和可变长度的二进制字符串

　　　　BLOB：二进制数据的可变长度非结构化集合

类	类型	说明
二进制	BINARY	类似于 CHAR（固定长度）类型，但存储的是二进制字节字符串，而不是非二进制字符串
二进制	VARBINARY	类似于 VARCHAR（可变长度）类型，但存储的是二进制字节字符串，而不是非二进制字符串
BLOB	TINYBLOB	最大长度为255 个字节的 BLOB 列
BLOB	BLOB	最大长度为65,535 个字节的 BLOB 列
BLOB	MEDIUDMBLOB	最大长度为16,777,215 个字节的 BLOB 列
BLOB	LONGBLOB	最大长度为4,294,967,295 个字节的 BLOB 列

1.4.7 时间数据类型

类型	格式	示例
DATE	YYYY-MM-DD	2017-12-16
TIME	hh:mm:ss[.uuuuuu]	12:59:02.123456
DATETIME	YYYY-MM-DD hh:mm:ss[.uuuuuu]	2017-12-16 12:59:02.123
TIMESTAMP	YYYY-MM-DD hh:mm:ss[.uuuuuu]	2017-12-16 12:59:02.12
YEAR	YYYY	2017

1.4.8 列属性

　　列属性的类别：

　　　　数值：适用于数值数据类型（BIT 除外）

　　　　字符串：适用于非二进制字符串数据类型

　　　　常规：适用于所有数据类型

数据类型	属性	说明
数值	UNSIGNED	禁止使用负值
仅整数	AUTO_INCREMENT	生成包含连续唯一整数值的序列
字符串	CHARACTER SET	指定要使用的字符集
字符串	COLLATE	指定字符集整理
字符串	BINARY	指定二进制整理
全部*	NULL 或 NOT NULL	指示列是否可以包含 NULL 值
全部	DEFAULT	如果未为新记录指定值，则为其提供默认值

1.4.9 数据类型小结

　　使用固定长度数据类型：

　　　　如果存储的所有字符串值的长度相同

　　使用可变长度数据类型：

　　　　如果存储的字符串值不同、对于多字节字符集

　　对于频繁使用的字符，使用占用空间较少的多字节字符集。

　　　　使用基本多文种平面 (Basic Multilingual Plane, BMP) 之外的其他、Unicode 字符集。

1.5开发人员SQL规范:

　　1、合适的数据类型
　　2、定义合理的数据长度
　　3、建表时，同时设置字符集、校对规则、存储引擎类型
　　4、表名、列名必须有意义，每个列要有描述字段（注释字段 COMMENT '素材类型'）
　　5、每个表都，定义无关列，设置为自增长（AUTO_INCREMENT）
　　6、每个表都有主键列，一般是设置在无关列上 PRIMARY KEY (`ID`)
　　7、每个列都是not null ，default。
　　8、列要加合适的约束条件。
　　9、插入数据的时候不要有空值

1.6 参考文献

http://blog.51cto.com/oldboy/1321061          防止人为误操作MySQL数据库技巧一例

http://www.cnblogs.com/clsn/p/8038964.html

本文出自“惨绿少年”，欢迎转载，转载请注明出处！http://blog.znix.top

MySQL用户管理及SQL语句详解

1.1 MySQL用户管理

1.1.1 用户的定义

1.1.2 用户的作用

1.1.3 连接数据库

1.1.4 【练习题】按照要求创建用户

1.1.5 用户删除

1.1.6 用户授权

1.2 MySQL 客户端工具

1.2.1 MysQL客户端命令介绍

1.2.2 mysql命令说明

1.2.2.1 mysql中help命令的使用

1.2.2.2 source命令的使用

1.2.3 mysqladmin命令说明

1.2.4 mysqldump简单说明

1.3 SQL语句入门

1.3.1 DDL语句(数据定义语言)

1.3.1.1 数据库查看

1.3.1.2 数据库操作

1.3.1.3 DDL语句之管理表

1.3.1.4 修改表的定义

1.3.1.5 DDL语句小结

1.3.2 DCL数据库控制语言

1.3.3 DML数据库操作语言

1.3.3.1 insert 语句

1.3.3.2 update更改数据库(一定要加上where条件)

1.3.3.3 delete删除表内容(一定要有where条件)

1.3.3.4 生产中的伪删除

1.3.3.5 防止不加条件误删【安全】

1.3.4 DQL数据查询语言标准语法

1.3.4.1 语法说明

1.3.4.2 查看操作

1.3.5 字符集说明

1.3.5.1 常用的字符集

1.3.5.2 字符集设置

1.3.5.3 MySQL客户端级别（连接及返回结果）

1.3.6 常用的查询操作

1.4 数据类型说明

1.4.1 ER模型

1.4.2 数据类型介绍

1.4.3 创建带有数据类型的表

1.4.4 数值数据类型

1.4.5 字符串数据类型

1.4.6 二进制字符串数据类型

1.4.7 时间数据类型

1.4.8 列属性

1.4.9 数据类型小结

1.5开发人员SQL规范:

1.6 参考文献

相关文章