MySQL 基础SQL语句

Linux系统环境

[root@mysql ~]# cat /etc/redhat-release                     #==》系统版本
CentOS release 6.7 (Final)
[root@mysql ~]# uname –r                                    #==》内核版本
2.6.32-573.el6.x86_64
[root@mysql ~]# uname -m                                    #==》系统架构
x86_64
[root@mysql ~]# echo $LANG                                  #==》系统字符集
en_US.UTF-8
[root@mysql ~]# mysql –V                                    #==》MySQL版本
mysql  Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1

MySQL配置文件
提示：/application/mysql是MySQL程序目录

#==》MySQL启动脚本，一般复制到/etc/init.d/mysqld
/application/mysql/support-files/mysql.server   

#==》MySQL主配置文件，一 般复制到/etc/my.cnf
/application/mysql/support-files/my-small.cnf /etc/my.cnf   

#==》MySQL所有二进制命令存放目录，可复制到/usr/local/sbin目录下或者添加环境变量
/application/mysql/bin/

#==》MySQL错误日志
/application/mysql/data/ MySQL01.err

#==》MySQL默认端口 3306
[root@mysql ~]# netstat -tlunp | grep 3306
tcp 0      0 0.0.0.0:3306        0.0.0.0:*        LISTEN      4780/mysqld

#==》MySQL套接字文件sock
/application/mysql-5.5.32/tmp/mysql.sock

什么是SQL

SQL英文全称Structured Query Language，中文意思是结构化查询语言，它是一种数据查询和程序设计语言，对关系数据库中的数据进行定义和操作的语言方法，是大多数关系数据库管理系统所支持。

SQL结构化查询语言分类
1、数据查询语言（DQL）

DQL全称Data Query Language，其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING.这些DQL保留字常与其他类型的SQL语句一起使用。具体语句例如：
mysql> select user,host,password from mysql.user;

2、数据操作语言（DML）

DML全称Data Manipulation Language，其语句包括动词INSERT，UPDATE和DELETE.它们分别用于添加，修改和删除表中的行（数据）。也称为动作查询语言。具体语句例如：
mysql> delete from mysql.user where user='oldboy' and host='localhost';

3、事务处理语言（TPL）

它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK.

4、数据控制语言（DCL）

DCL全称（Data Control Language），它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
mysql> grant all privileges on test.* to 'xiaoming'@'localhost' identified by '123456';

5、数据定义语言（DDL）

DDL全称（Data Definition Language），其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE或DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字，它也是动作查询的一部分。

6、指针控制语言（CCL）

CCL全称（CURSOR Control Language），它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

7、SQL语句最常见的分类一般就是3类：

    #==》运维常用
（1）、DDL一数据定义语言（CREATE，ALTER，DROP）
    #==》开发常用
（2）、DML一数据操作语言（SELECT，INSERT，DELETE，UPDATE）
    #==》运维常用
（3）、DCL一数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）

一、MySQL创建数据库

命令语法 : create database 数据库名称          #==》数据库名不能数字开头

mysql> create database db01;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db01               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> help create                        #==》查看create命令帮助，非常有用

二、MySQL创建数据库并指定字符集
标注：MySQL支持多种字符集，在编译安装MySQL可以指定所支持字符集，同时可以设置MySQL默认字符集，如果没有指定默认字符集，MySQL默认字符集是lating1拉丁字符

1、查看库对应的字符集
mysql> show create database db01\G
*************************** 1. row ***************************
       Database: db01
Create Database: CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

2、创建db02数据库并指定gbk字符集
mysql> create database db02 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database db02\G
*************************** 1. row ***************************
       Database: db02
Create Database: CREATE DATABASE `db02` /*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.00 sec)

3、创建db03数据库并指定utf8字符集
mysql> create database db03 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> show create database db03\G
*************************** 1. row ***************************
       Database: db03
Create Database: CREATE DATABASE `db03` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

4、MySQL编译安装支持字符集类型并指定默认utf8字符集
-DDEFAULT_CHARSET=utf8 \                    #==》指定默认utf8字符集
-DDEFAULT_COLLATION=utf8_general_ci \       #==》指定默认utf8字符集
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \        #==》安装支持的字符集

三、MySQL显示数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db01               |
| db02               |
| db03               |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

mysql> help show                            #==》查看show命令帮助，非常有用
mysql> show databases like '%db%';          #==》%为通配符，匹配所有内容
+-----------------+
| Database (%db%) |
+-----------------+
| db01            |
| db02            |
| db03            |
+-----------------+
3 rows in set (0.00 sec)
mysql> use db01;                            #==》进入db01数据库
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| db01       |
+------------+
1 row in set (0.00 sec)

四、MySQL删除数据库

mysql> show databases like 'db%';
+----------------+
| Database (db%) |
+----------------+
| db01           |
| db02           |
| db03           |
+----------------+
3 rows in set (0.00 sec)
mysql> drop database db03;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases like 'db%';
+----------------+
| Database (db%) |
+----------------+
| db01           |
| db02           |
+----------------+
2 rows in set (0.01 sec)
mysql> help drop database                   #==》学习潜意识就要查看帮助

五、MySQL连接数据库

语法格式：use 数据库名

mysql> use db01                             #==》进入db01数据库
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| db01       |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.32    |
+-----------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-07-26 14:31:44 |
+---------------------+
1 row in set (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)

mysql> show tables like 'user';                 #==》连接到mysql数据库查询指定表
+------------------------+
| Tables_in_mysql (user) |
+------------------------+
| user                   |
+------------------------+
1 row in set (0.00 sec)

mysql> show tables from mysql like 'user';      #==》没有连接到mysql数据库查询指定表
+------------------------+
| Tables_in_mysql (user) |
+------------------------+
| user                   |
+------------------------+
1 row in set (0.00 sec)

六、删除MySQL数据库多余账号
标注：如果drop删除不了(一般是特殊字符或大写)，可以使用delete from语句进行删除

mysql> create user 'test01'@'localhost' identified by '123456';     #==》创建测试用户
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'test02'@'localhost' identified by '123456';     #==》创建测试用户
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+--------+-----------+-------------------------------------------+
| user   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| root   | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root   | 127.0.0.1 |                                           |
| test02 | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| test01 | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> drop user 'test01'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> delete from mysql.user where user='test02' and host='localhost';
Query OK, 1 row affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 |                                           |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> flush privileges;                    #==》一定要刷新，否则不生效
Query OK, 0 rows affected (0.00 sec)
mysql> help drop                            #==》多查帮助，养成习惯

七、创建MySQL用户并赋予用户权限

语法格式：
授权命令    对应权限           目录：库和表      用户名和客户端主机        用户密码
grant      all privileges     on db01.*     to username@localhost   identified by ‘password’

1、一条命令创建用户并授权权限

mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 |                                           |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant all privileges on db01.* to 'xiaoming'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+----------+-----------+-------------------------------------------+
| user     | host      | password                                  |
+----------+-----------+-------------------------------------------+
| root     | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root     | 127.0.0.1 |                                           |
| xiaoming | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for 'xiaoming'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for xiaoming@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoming'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `db01`.* TO 'xiaoming'@'localhost'                                                      |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)    

mysql> flush privileges;                    #==》一定要刷新，否则不生效
Query OK, 0 rows affected (0.00 sec)

2、先创建用户在授权权限

mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 |                                           |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> create user 'zhangshang'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on db01.* to 'zhangshang'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+------------+-----------+-------------------------------------------+
| user       | host      | password                                  |
+------------+-----------+-------------------------------------------+
| root       | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root       | 127.0.0.1 |                                           |
| zhangshang | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for 'zhangshang'@'localhost';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for zhangshang@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangshang'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `db01`.* TO 'zhangshang'@'localhost'                                                      |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> flush privileges;                #==》刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> help grant all                   #==》多查看帮忙，养成习惯

3、授权指定IP远程连接数据库

（1）、单独IP匹配
mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)

mysql> grant all privileges on db3.* to 'xiaozhang'@'10.0.0.200' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+-----------+------------+
| user      | host       |
+-----------+------------+
| xiaozhang | 10.0.0.200 |
| root      | 127.0.0.1  |
| root      | localhost  |
+-----------+------------+
3 rows in set (0.00 sec)

（2）、百分号匹配
mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)

mysql> grant all on db01.* to 'xiaoming'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| xiaoming | 10.0.0.%  |
| root     | 127.0.0.1 |
| root     | localhost |
+----------+-----------+
3 rows in set (0.00 sec)

（3）、网段匹配
mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)

mysql> grant all on db02.* to 'xiaohong'@'10.0.0.0/255.255.255.0' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+----------+------------------------+
| user     | host                   |
+----------+------------------------+
| xiaohong | 10.0.0.0/255.255.255.0 |
| root     | 127.0.0.1              |
| root     | localhost              |
+----------+------------------------+
3 rows in set (0.00 sec)

4、查看用户可以授权类别

mysql> show grants for 'xiaozhang'@'10.0.0.200';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for xiaozhang@10.0.0.200                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaozhang'@'10.0.0.200' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `db3`.* TO 'xiaozhang'@'10.0.0.200'                                                       |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'xiaozhang'@'10.0.0.200'                                                       |
+-------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> revoke insert on db01.* from 'xiaozhang'@'10.0.0.200';        #==》指定撤回插入权限
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'xiaozhang'@'10.0.0.200';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for xiaozhang@10.0.0.200                                                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaozhang'@'10.0.0.200' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'                                                                                                                 |
| GRANT ALL PRIVILEGES ON `db3`.* TO 'xiaozhang'@'10.0.0.200'                                                                                                                                                                       |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `db01`.* TO 'xiaozhang'@'10.0.0.200' |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
#==》查看用户指定对应的表权限
mysql> select * from mysql.user where user='xiaozhang'\G    
mysql> help grant                                               #==》多查看帮忙，养成习惯

5、企业生产环境如何授权用户权限

常规情况下只授权  select/insert/update/delete  4个权限即可

八、MySQL回收指定权限或所有权限回收

mysql> show grants for 'xiaoming'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for xiaoming@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoming'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `db01`.* TO 'xiaoming'@'localhost'                                                      |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke INSERT,SELECT,DELETE,UPDATE on db01.* from 'xiaoming'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'xiaoming'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for xiaoming@localhost                                                                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoming'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'                                                                                          |
| GRANT CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `db01`.* TO 'xiaoming'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'xiaoming'@'localhost';
Query OK, 0 rows affected (0.00 sec)

九、MySQL设置及修改用户密码
1、删除root管理用户，创建system管理员替换root管理用户（root与system等价）

mysql> select user,host from mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| root     | 127.0.0.1 |
| root     | localhost |
| xiaoming | localhost |
+----------+-----------+
3 rows in set (0.00 sec)

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

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

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

mysql> grant all privileges on *.* to 'system'@'localhost' identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'system'@'127.0.0.1' identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| system | 127.0.0.1 |
| system | localhost |
+--------+-----------+
2 rows in set (0.00 sec)
mysql> flush privileges;                #==》一定要刷新权限，否则不生效
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'system'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for system@localhost                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'system'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2、MySQL修改密码三种方法

（1）、方法一：命令行外修改方法
[root@mysql ~]# mysqladmin -u root -p'123456'  password 'ssti123'
[root@mysql ~]# mysqladmin -u root -p  password 'ssti123'

（2）、方法二：sql语句update命令修改
标注：使用update命令修改用户密码，必须遵守两点：
（2.1）、必须指定where条件
（2.2）、必须使用password()函数加密更改密码，否则修改的账号密码登录失败
mysql> update mysql.user set password=password("123456") where user="root" and host="localhost";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;                    #==》一定要刷新权限，否则不生效
Query OK, 0 rows affected (0.00 sec)

（3）、方法三：
标注：此命令只能修改当前登录用户的密码
mysql> set password=password("123456");
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;                    #==》一定要刷新权限，否则不生效
Query OK, 0 rows affected (0.00 sec)

十、MySQL 表操作

语法格式：
create table <表名> (
    <字段名1> <类型1>,
    <字段名2> <类型2>,
    <字段名3> <类型3>,
<字段名1> <类型1>);

1、创建表

mysql> create database testdb DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)

mysql> use testdb
Database changed
mysql>

#==》第一种建表语句
create table student(
id int(4) not null,
name char(20) not null,
aga tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL
);

#==》第二种建表语句
create table student02(
`id` int(4) not null,                         #==》学号，数字类型，长度为4，不为空值
`name` char(20) not null,                     #==》姓名，定长字符类型，长度为20，不为空值
`aga` tinyint(2) NOT NULL default '0',        #==》年龄，数字类型，长度为2，不为空，默认0值 
`dept` varchar(16) default NULL               #==》系，变长字符类型，长度为16，默认为空
)ENGINE=InnoDB DEFAULT CHARSET=gbk;           #==》引擎和字符集，默认引擎为InnoDB，字符集，继承库的gbk

2、查看表结构

mysql> use testdb
Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)
mysql> show create table student\G                            #==》查已建表的语句（可看索引及创建表的信息）
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL,
  `aga` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
mysql> desc student;                                           #==》查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| aga   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show columns from student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| aga   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

十一、表插入数据insert

命令格式： insert inot <表名>（字段1，字段2…….） values(值1，值2…….)
表插入优化之一：insert inot 插入数据尽量批量插入

mysql> 
create table student(
id int(4) not null AUTO_INCREMENT,
name char(20) not null,
primary key(`id`)
);
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
mysql> show create table student;
mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student(id,name) values(1,'xiaoming');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student(id,name) values(2,'zhangshang'); #==》一条数据插入
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(id,name) values(3,'lishi');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------------+
| id | name       |
+----+------------+
|  1 | xiaoming   |
|  2 | zhangshang |
|  3 | lishi      |
+----+------------+
mysql> insert into student(id,name) values(4,'wangwu'),(5,'Tom');  #==》批量插入数据
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------------+
| id | name       |
+----+------------+
|  1 | xiaoming   |
|  2 | zhangshang |
|  3 | lishi      |
|  4 | wangwu     |
|  5 | Tom        |
+----+------------+
5 rows in set (0.00 sec)
3 rows in set (0 mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec).00 sec)
mysql> help insert

十二、表查询数据select

命令格式：select  <字段1>,<字段2>……  from  <表名>  where  <表达式>，其中，select/from/where不能随便改，支持大小写
表查询优化之一：select 查询尽量指定明确的字段，这样查询效率高

1、单表查询

mysql> use testdb
Database changed
mysql> select * from student;
+----+------------+
| id | name       |
+----+------------+
|  1 | xiaoming   |
|  2 | zhangshang |
|  3 | lishi      |
|  4 | wangwu     |
|  5 | Tom        |
+----+------------+
5 rows in set (0.00 sec)

mysql> select id,name from student;
+----+------------+
| id | name       |
+----+------------+
|  1 | xiaoming   |
|  2 | zhangshang |
|  3 | lishi      |
|  4 | wangwu     |
|  5 | Tom        |
+----+------------+
5 rows in set (0.00 sec)
mysql> select id,name from student limit 2;
+----+------------+
| id | name       |
+----+------------+
|  1 | xiaoming   |
|  2 | zhangshang |
+----+------------+
2 rows in set (0.00 sec)
mysql> select id,name from student where id=1;
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaoming |
+----+----------+
1 row in set (0.00 sec)
mysql> select id,name from student where id > 1 and id < 3;
+----+------------+
| id | name       |
+----+------------+
|  2 | zhangshang |
+----+------------+
1 row in set (0.00 sec)
mysql> select id,name from student where name='xiaoming' or id=3;
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaoming |
|  3 | lishi    |
+----+----------+
2 rows in set (0.00 sec)

2、多表查询

mysql> select * from student01;
+----+------------+-------+
| id | name       | class |
+----+------------+-------+
|  1 | xiaoming   | 1班   |
| 21 | xiaozhang  | 2班   |
| 33 | xiaohong   | 2班   |
|  2 | xiaocheng  | 2班   |
|  3 | zhangshang | 3班   |
|  4 | lishi      | 3班   |
+----+------------+-------+
6 rows in set (0.00 sec)

mysql> select * from grade;
+----+--------+---------+
| id | course | chengji |
+----+--------+---------+
|  1 | shuxue | 78      |
| 21 | shuxue | 88      |
|  2 | shuxue | 93      |
|  3 | shuxue | 45      |
+----+--------+---------+
4 rows in set (0.00 sec)

mysql>select student01.id,student01.name,grade.course,grade.chengji from student01,grade where student01.id=grade.id;
+----+------------+--------+---------+
| id | name       | course | chengji |
+----+------------+--------+---------+
|  1 | xiaoming   | shuxue | 78      |
| 21 | xiaozhang  | shuxue | 88      |
|  2 | xiaocheng  | shuxue | 93      |
|  3 | zhangshang | shuxue | 45      |
+----+------------+--------+---------+
4 rows in set (0.00 sec)

十三、使用explain查询select 查询语句执行计划，即可以判断是否引用索引情况

mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> select * from student;
+----+------------+
| id | name       |
+----+------------+
|  1 | xiaoming   |
|  2 | zhangshang |
|  3 | lishi      |
|  4 | wangwu     |
|  5 | Tom        |
+----+------------+
5 rows in set (0.00 sec)

mysql> explain select id,name from student where name="wangwu"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where
1 row in set (0.00 sec)

mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   | MUL | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> explain select id,name from student where name="wangwu"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: index_name
          key: index_name
      key_len: 40
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> help explain                           #==》多查看帮助

十四、修改表中指定条件固定列数据update

命令格式：  update <表名>  set 字段=新值 where 条件（一定要添加条件并且注意条件）
注意：如果update后面没有添加where条件语句会把表中的数据都修改了，这是很严重的问题。

mysql> select * from student;
+----+------------+
| id | name       |
+----+------------+
|  3 | lishi      |
|  5 | Tom        |
|  4 | wangwu     |
|  1 | xiaoming   |
|  2 | zhangshang |
+----+------------+
5 rows in set (0.00 sec)
mysql> update student set name='oldboy' where name='Tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+------------+
| id | name       |
+----+------------+
|  3 | lishi      |
|  5 | oldboy     |
|  4 | wangwu     |
|  1 | xiaoming   |
|  2 | zhangshang |
+----+------------+
5 rows in set (0.00 sec)

update防止误更新(update)操作办法
[root@mysql ~]# echo " alias mysql='mysql -U'" >> /etc/profile
[root@mysql ~]# tail -1 /etc/profile
 alias mysql='mysql -U'
[root@mysql ~]# mysql -uroot -p123456
mysql> use testdb
Database changed
mysql> select * from student;
+----+------------+
| id | name       |
+----+------------+
|  3 | lishi      |
|  5 | oldboy     |
|  4 | wangwu     |
|  1 | xiaoming   |
|  2 | zhangshang |
+----+------------+
5 rows in set (0.00 sec)
mysql> update student set name="oldgirl";
#==》报错原因是没有添加where条件，防止update误操作更新
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> update student set name="oldgirl" where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  3 | lishi    |
|  5 | oldboy   |
|  2 | oldgirl  |
|  4 | wangwu   |
|  1 | xiaoming |
+----+----------+
5 rows in set (0.00 sec)

十五、删除表中数据delete

命令格式： delete from <表名> where 条件（delete一定要加条件，否则整个表都会删除，此命令操作需要谨慎）

1、delete 删除表中数据（逻辑一行一行删除）

mysql> use testdb
Database changed
mysql> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  3 | lishi    |
|  5 | oldboy   |
|  2 | oldgirl  |
|  4 | wangwu   |
|  1 | xiaoming |
+----+----------+
5 rows in set (0.00 sec)

mysql> delete from student where id=4 and name='wangwu';
Query OK, 1 row affected (0.02 sec)

mysql> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  3 | lishi    |
|  5 | oldboy   |
|  2 | oldgirl  |
|  1 | xiaoming |
+----+----------+
4 rows in set (0.00 sec)
mysql> delete from student where id>4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  3 | lishi    |
|  2 | oldgirl  |
|  1 | xiaoming |
+----+----------+
3 rows in set (0.00 sec)

2、truncate 清空表中所有内容（物理清空，直接删除文件）

mysql> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  3 | lishi    |
|  2 | oldgirl  |
|  1 | xiaoming |
+----+----------+
3 rows in set (0.00 sec)

mysql> truncate table student;                      #==》此命令操作需要谨慎
Query OK, 0 rows affected (0.01 sec)
mysql> select * from student;
Empty set (0.00 sec)

十六、增删改表中的字段 alter table

命令格式：alter table <表名> add 字段 类型  其它;
mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   | MUL | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

#==》默认age字段添加在表中最后一列
mysql> alter table student add age int(3);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   | MUL | NULL    |                |
| age   | int(3)   | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

#==》指定在name字段后面添加sex字段
mysql> alter table student add sex char(6) after name;      
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   | MUL | NULL    |                |
| sex   | char(6)  | YES  |     | NULL    |                |
| age   | int(3)   | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
#==》指定xuehao字段s添加到表中第一列
mysql> alter table student add xuehao int(10) first;    
Query OK, 4 rows affected (0.15 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| xuehao | int(10)  | YES  |     | NULL    |                |
| id     | int(4)   | NO   | PRI | NULL    | auto_increment |
| name   | char(20) | NO   | MUL | NULL    |                |
| sex    | char(6)  | YES  |     | NULL    |                |
| age    | int(3)   | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

#==》删除指定xuehao字段
mysql> alter table student drop xuehao;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   | MUL | NULL    |                |
| sex   | char(6)  | YES  |     | NULL    |                |
| age   | int(3)   | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
#==》修改age字段类型int型修改为char型
mysql> alter table student modify age char(20);
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   | MUL | NULL    |                |
| sex   | char(6)  | YES  |     | NULL    |                |
| age   | char(20) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec) 

#==》修改age字段名称及类型
mysql> alter table student change age kecheng varchar(10);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(4)      | NO   | PRI | NULL    | auto_increment |
| name    | char(20)    | NO   | MUL | NULL    |                |
| sex     | char(6)     | YES  |     | NULL    |                |
| kecheng | varchar(10) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

十七、修改表名rename

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql> rename table student to xuesheng;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| xuesheng         |
+------------------+
1 row in set (0.00 sec)

mysql> alter table xuesheng rename to student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

十八、删除表或库 drop

1、删除库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| wiki               |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database wiki;              #==》删除wiki库
Query OK, 0 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)

2、删除表

mysql> use testdb
Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| bk01             |
| student          |
+------------------+
2 rows in set (0.00 sec)

mysql> drop table bk01;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,189评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,577评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,857评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,703评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,705评论 5赞 366
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,620评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,995评论 3赞 396
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,656评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,898评论 1赞 298
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,639评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,720评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,395评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,982评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,953评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,195评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,907评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,472评论 2赞 342

MySQL 基础SQL语句

推荐阅读更多精彩内容