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)
网友评论