MySQL系列（二）：MySQL常用基础命令
启动与关闭MySQL
单实例MySQL启动与关闭方法
常规启动关闭数据库方式（推荐）
1）启动MySQL命令：
[root@mysql-1 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!2）查看MySQL端口：
[root@mysql-1 ~]# ss -lnt|grep 3306
LISTEN 0 50 *:3306 *:*3）查看MySQL进程：
4）MySQL启动基本原理说明：
/etc/init.d/mysqld是一个shell启动脚本，启动后终会调用mysqld_safe脚本，最后调用mysqld服务启动mysql，如下，/etc/init.d/mysqld脚本中调用mysql_safe的程序
$bindir/mysql_safr --datadir="$datadir" --pid-file="$mysql_pid_file_path" $ other_args >/dev/null 2>&1 &初始化MySQL时输出提示的启动方法
mysql_safe --user=mysql &提示：
（1）当忘记mysql的root密码是，会经常使用mysqld_safe --user=mysql & 带参数启动。
（2）我们自己开发脚本启动数据库是可能会用到这个启动方法。
（3）/etc/init.d/mysqld和mysqld_safe --user=mysql &的启动实质是一样的。
常规方法关闭数据库
（1）关闭MySQL命令
[root@mysql-1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
[root@mysql-1 ~]# ss -lnt|grep 3306提示：使用编译及二进制安装的mysql，mysql的启动脚本由mysql-xx/support-files/mysql.server 复制而来。
（2）强制关闭数据库方法：
killall mysqld
pkill mysqld
killall -9 mysqld
mysql: no process killed
kill pid提示：
用killall关闭，要直到出现mysqld: no process killed表示完成关闭操作。强制关闭命令不建议在生产环境使用，尽量不要野蛮粗鲁杀死数据库，生产高并发环境可能会引起数据丢失。
（3）优雅关闭数据库方法
第一种mysqladmin方法：
mysqladmin -uroot -pyjscloud shutdown第二种自带的脚本
/etc/init.d/mysqld stop第三种kill信号的方法
kill -USE2 'cat path/pid'提示：推荐前两种方法，自己写脚本启动停止就用第一个
登陆MySQL方法
单实例MySQL登陆的方法
mysql #刚刚安装mysql无密码情况的登陆方式
mysql -uroot #刚刚安装mysql无密码情况的登陆方式
mysql -uroot -p #这是标准的dba命令行登陆命令
mysql -uroot -pyjscloud #非脚本里一般不这样用。密码明文会泄露密码，可以掩饰history功能解决强制linux不记录敏感历史命令
HISTCONTROL=ignorespace
history -c
history -d 5登陆后默认提示符是：mysql>，这个提示符也是可以改的，就像linux命令行提示符也可以改一样。
更改MySQL数据库登陆提示（了解即可）方法如下：
mysql> prompt \u%yjscloud \r:\m:\s->
PROMPT set to '\u%yjscloud \r:\m:\s->'
root%yjscloud 08:04:07->配置文件修改登陆提示提示符：
在my.cnf配置文件中[mysql]模块下添加如下内容（注意，不是[mysqld]），保存后，无需重启mysql，退出当前session，重新登录即可如果my.cnf配置文件中加，可用\，避免转义带来的问题。
[mysql]
prompt=\\u%yjscloud \\r:\\m:\\s->善用MySQL的帮助命令help
linux的帮助man，help
mysql中的help命令和linux命令行的man是类似的，和linux下查看内置命令的help是同名的。要想查看mysql中的命令使用语法，就需要用help，help后面接相关命令及命令组合即可，例如：help create ，默认情况mysql中的命令执行不区分大小写，这点和linux命令行严格区分大小写是有区别的。
排错的几点建议：
1、一般解决故障问题，一定要先看故障日志，根据报错输出或者日志文件自行排查下，别忘了这是上帝给你的难得的提示的机会，一旦错过提示机会就让给了别人！
2、实在解决不了，然后再搜索（g>b）好些，搜索关键字要精练，且尽量唯一。
3、对于知识点的学习、用法，查看软件的帮助或官方手册最好，直接的一手资料。
4、实在看不懂英文的，可以先搜索中文资料，然后边对照边学习英文软件的帮助或官方手册，习惯了就会看英文了。网上大部分中文资料都是不全甚至是有问题的，没有变通能力的菜鸟很难搞定的。培养自身的学习习惯很重要，掌握了学习方法，思维方式，锻炼了学习能力，才会举一反三，真正的成长起来。
退出MySQL数据库方法
mysql>quit或
eixt
设置及修改mysql root用户密码
MySQL数据库用户安全策略介绍
安装mysql数据库后，默认的管理员root密码为空，这很不安全。因此需要设置一个密码。其实在安装MySQL单实例后，我们已经做了一些安全措施；例如：
a、为root设置密码
b、删除无用的mysql库内的用户账户
c、删除默认存在的test数据库
除了上面的方法，针对MySQL数据库的用户处理，我们还是有严格的做法如下。
更安全的措施例如删除root,添加新的管理员用户
1）删除所有mysql中的用户，包括root超级用户。
mysql> delete from mysql.user;
Query OK, 7 rows affected (0.01 sec)提示：root可以保留，然后修改为其他用户也可以
2）增加system并提升为超级管理员，即和root等价的用户，只是名字不同。
mysql> grant all privileges on *.* to system@'localhost' identified by'yjscloud' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)为管理员root用户设置密码方法
mysqladmin -uroot password 'yjscloud123' #没有密码的用户设置密码命令，这个是linux命令行不是mysql命令行修改管理员root密码
1）方法一：命令行外修改
mysqladmin -uroot -p'yjscloud123' password 'yjscloud456'2)方法二：sql语句修改
mysql> update mysql.user set password=password("yjscloud789") where user='system';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)提示：此方法合适密码丢失后通过--skip-grant-tables参数启动数据库后修改密码。
mysql> select user,host from mysql.user;
+----------+----------------+
| user | host |
+----------+----------------+
| yjscloud | 192.168.0.0/24 |
| root | localhost |
| system | localhost |
+----------+----------------+
3 rows in set (0.00 sec)
mysql> update mysql.user set password=password("yjscloud123") 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)提示：
1、必须指定where条件
2、必须使用password（）函数来加密更改密码
找回丢失的mysql root用户密码
启动修改丢失的MySQL实例root密码方法
1）首先停止mysql
[root@mysql-1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!2）使用--skip-grant-tables启动mysql，忽略授权登陆验证
mysqld_safe --skip-grant-tables --user=mysql &
mysql -uroot -p #登陆时密码为空3）无需密码即可登陆MySQL
4）修改root密码为新密码
mysql> update mysql.user set password=password('yjscloud000') 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.01 sec)
mysql> quit
Bye5）重启服务再登陆
SQL结构化查询语言
什么是SQL
SQL，英文全称Structured Query Langyuage，中文意思是结构化查询语言，它是一种对关系数据库中的数据进行定义和操作的语言方法，是大多数关系数据库管理系统所支持的工业标准。结构化查询语言SQL是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据存放方式，所以，具有完全不同于底层结构的，不同数据库系统可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使得它具有极大的灵活性和强大的功能。不同的数据库系统的SQL语言会有一些差别。
SQL结构化查询语言包含6个部分
1）数据查询语言(DQL):
DQL全称Data Query Language,其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎么在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得多的动词，其他DQL常用的保留字有WHERE,ORDER BY,GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起用。具体语句例如：
mysql> select user,host from mysql.user order by user;
+----------+----------------+
| user | host |
+----------+----------------+
| root | localhost |
| system | localhost |
| yjscloud | 192.168.0.0/24 |
+----------+----------------+
3 rows in set (0.00 sec)2）数据操作语言（DML）
DML全称Data Manipulation Language,其语句包括动词INSERT,UPDATE和DELETE。它们分别用于添加，修改和删除表中的行（数据）。也称为查询语言。具体语句例如：
mysql> delete from mysql.user where user='yjscloud';
Query OK, 1 row affected (0.00 sec)3）事务处理语言（TPL）
它的语句能确保被DML语句影响的表所有行及时得以更新。TPL语句BEGIN,TRANSACTION,COMMIT和ROLLBACK。
4）数据控制语言（DCL）
DCL全称（Data Control Language），它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列表的访问。
5）数据定义语言（DDL）
全称，（Data Definition Language），其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREATE TABLE或DROP TABLE）;为表加入索引等。DDL包括许多与数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
6）指针控制语言（CCL）
全称CURSOR Control Language，它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独的操作。
小结：SQL语句最常见的分类一般就是3类：
DDL——数据定义语言（CREATE,ALTER,DROP） <----运维
DML——数据操作语言（SELECT,INSERT,DELETE,UPDATE）<----开发
DCL——数据控制语言（GRANT,REVOKE,COMMIT,ROLLBACK）<----运维
MySQL数据常见管理应用
创建数据库
命令语法：create database <数据库名> #注意库名不能数字开头
在MySQL默认字符集情况下建立数据库测试如下：
a、建立名为yjscloud和yjscloud1的数据库（我在安装数据库时指定了utf-8为默认的字符集）
mysql> create database yjscloud;
Query OK, 1 row affected (0.00 sec)
mysql> create database yjscloud1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases like 'yjscloud%';
+----------------------+
| Database (yjscloud%) |
+----------------------+
| yjscloud |
| yjscloud1 |
+----------------------+
2 rows in set (0.00 sec)
mysql> show create database yjscloud;
+---------- +-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| yjscloud | CREATE DATABASE yjscloud /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)b、建立一个名为yjscloud_gbk的GBK字符集数据库
mysql> create database yjscloud_gbk default character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database yjscloud_gbk;
+--------------+----------------------------------------------------------------------+
| Database | Create Database |
+--------------+----------------------------------------------------------------------+
| yjscloud_gbk | CREATE DATABASE yjscloud_gbk /*!40100 DEFAULT CHARACTER SET gbk */ |
+--------------+----------------------------------------------------------------------+
1 row in set (0.00 sec)提示：字符集的不一致时数据库中文内容乱码的罪魁祸首，在编译安装的时候指定了字符集则以后创建对应的数据库就不需要指定字符集了。
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \如果在编译的时候没有指定字符集或者指定了不同的字符集，如何解决？我们只需要指定字符集创建数据库即可
create database yjscloud_gbk default character set gbk collate gbk_chinese_ci; #创建gbk字符集数据库
create database yjscloud_utf8 default character set utf8 collate utf8_chinese_ci; #创建utf8字符集数据库显示数据库
命令：show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yjscloud |
| yjscloud1 |
| yjscloud_gbk |
+--------------------+
6 rows in set (0.00 sec)
mysql> show databases like '%yjscloud%'; # %为通配符，匹配所有内容。
+----------------------+
| Database (yjscloud%) |
+----------------------+
| yjscloud |
| yjscloud1 |
| yjscloud_gbk |
+----------------------+
3 rows in set (0.00 sec)
删除数据库
命令：drop database <数据库名>
例如删除yjscloud的数据库
mysql> drop database yjscloud;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yjscloud1 |
| yjscloud_gbk |
+--------------------+
5 rows in set (0.00 sec)连接数据库
命令：use <数据库名> #use相当与linux命令行下的cd命令，use用于切换数据库
mysql> use yjscloud1
Database changed查看当前数据库包含的表信息
切到数据库里面去查看：
mysql> show tables;
Empty set (0.00 sec) #空表，因为是新库，还没有建表
mysql> show tables like 'user';
Empty set (0.00 sec)
mysql> show tables from yjscloud_gbk; #查看指定库中的表。
Empty set (0.00 sec)
mysql> show tables in yjscloud_gbk;
Empty set (0.01 sec)删除MySQL系统多余账号
语法：drop user "user"@"主机域" #注意引号，可以是单或双引号，但是不能不加。
mysql> select user,host from mysql.user;
+----------+-----------+
| user | host |
+----------+-----------+
| cloud | localhost |
| root | localhost |
| system | localhost |
| yjscloud | localhost |
+----------+-----------+
4 rows in set (0.00 sec)
mysql> drop user 'cloud'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+----------+-----------+
| user | host |
+----------+-----------+
| root | localhost |
| system | localhost |
| yjscloud | localhost |
+----------+-----------+
3 rows in set (0.00 sec)注意：删除后的结果如上面返回的结果如果drop删除不了（一般是特殊字符或大写），可以用下面方式删除（以root用户，yjscloud主机为例）
delete from mysql.user where user='root' and host='yjscloud';
flush privileges;创建MySQL用户及赋予用户权限
通过help查看grant命令帮助
...省略部分...
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
...省略部分...通过查看grant的命令帮助，可以很容易的找到创建用户并授权的例子！
1、使用grant命令在创建用户的同时进行权限授权：
GRANT ALL ON db1.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';2、上述grant命令帮助里还提供了一个先用create命令创建用户，然后再用grant授权的方法，即创建用户和授权权限分开进行，
例：
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';以上两条命令相当于：
GRANT ALL ON db1.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';通过grant命令创建用户并授权
grant命令简单语法如下：
grant all privileges on dbname.* to username@localhost identified by 'passwd';列表说明：
说明：上述命令是授权localhost主机上通过用户username管理dbname数据库的所有权限，密码为passwd。其中username，dbname，passwd可根据业务的情况修改。
create和grant配合法
1）首先创建用户username及密码passwd，授权主机localhost
create user 'username'@'localhost' identified by 'passwd';2）然后授权localhost主机上通过username管理dbname数据库的所有权限，无需密码。
grant all on dbname.* to 'username'@'localhost';3）实战操作：创建mycloud用户。对yjscloud库具备所有权限，允许从localhost主机登陆管理数据库，密码是mycloud123
1、查看当前数据库用户情况，然后执行对应命令创建用户：
查看用户mycloud的具体权限
开始对mycloud用户授权，并查看授权的权限细明
提示：可以看到默认权限是useage，即连接权限，后面又增加了all权限。
授权局域网内主机远程连接数据库
根据grant命令语法，我们知道yjscloud@'localhost'位置为授权访问数据库的主机，localhost可以用域名，ip地址或ip段来替代，因此，要授权局域网内主机可以通过如下方法实现：
1）百分号匹配法
mysql> grant all on *.* to yjscloud@'192.168.0.%' identified by 'yjscloud';
Query OK, 0 rows affected (0.00 sec)2）子网掩码配置法
mysql> grant all on *.* to yjscloud@'192.168.0.0/255.255.255.0' identified by 'yjscloud';
Query OK, 0 rows affected (0.00 sec)最后要刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)3）通过mysql客服端连接异地数据库服务：
1、本地mysql -uroot -pyjscloud 连接数据库相当与mysql -uroot -pyjscloud -h localhost
2、要远程连接192.168.0.30的数据库，命令为mysql -uyjscloud -pyjscloud -h 192.168.0.30，如果要能连接成功，还需要在192.168.0.30的数据库服务器上通过如下命令授权：
grant all on *.* to yjscloud@'192.168.0.%' identified by 'yjscloud';提示：上述命令意思是授权192.168.0.%，匹配这个字符串ip地址的的所有主机连接数据库，百分号%匹配所有192.168.0.0/24内的主机
MySQL用户可以授权的权限有哪些
1）先看看前面授权通过的mycloud的权限
mysql> show grants for 'mycloud'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for mycloud@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mycloud'@'localhost' IDENTIFIED BY PASSWORD '*5135EBEA729E32C95756AE6F914F901D66D2641D' |
| GRANT ALL PRIVILEGES ON yjscloud.* TO 'mycloud'@'localhost' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
#取消mycloud的选择权限
mysql> REVOKE SELECT ON yjscloud.* from 'mycloud'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'mycloud'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for mycloud@localhost |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mycloud'@'localhost' IDENTIFIED BY PASSWORD '*5135EBEA729E32C95756AE6F914F901D66D2641D' |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON yjscloud.* TO 'mycloud'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)提示：此时我们再查看oldboy用户权限，ALL PRIVILEGES权限已经被细分了，但是没有select权限了。在授权时，可以授权用户最小的满足业务需求的业务，而不是一味的授权ALL PRIVILEGES
企业生产环境如何授权用户权限
1）博客、CMS等产品的数据库授权
对于web连接用户授权尽量采用最小化原则，很多开源软件都是web界面安装，因此，在安装期间除了select，insert，update，delete4个权限外，还需要create，drop等比较危险的权限。
mysql> grant select,insert,update,delete,create,drop on blog.* to 'blog'@'192.168.0.%' identified by 'yjscloud';
Query OK, 0 rows affected (0.00 sec)常规情况下授权select，insert，update，delete4个权限即可，有的开源软件，例如discuz bbs，还需要create，drop等比较危险的权限。
2）生成数据库表后，要收回create，drop授权
mysql> show grants for 'blog'@'192.168.0.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for blog@192.168.0.% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'192.168.0.%' IDENTIFIED BY PASSWORD '*7C9FFD5B56F6DEE1126BC972C97B1DFEC8E4E089' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON blog.* TO 'blog'@'192.168.0.%' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
#收回create，drop授权
mysql> revoke create,drop on blog.* from 'blog'@'192.168.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'blog'@'192.168.0.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for blog@192.168.0.% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'192.168.0.%' IDENTIFIED BY PASSWORD '*7C9FFD5B56F6DEE1126BC972C97B1DFEC8E4E089' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON blog.* TO 'blog'@'192.168.0.%' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)3）生产环境针对主库（写为主读为辅）用户的授权：
普通的环境：
本机：lnmp，lamp环境数据库授权
grant all privileges on blog.* to 'blog'@'localhost' identified by 'yjscloud';应用服务器和数据库服务器不在一个主机上的授权：
grant all privileges on blog.* to 'blog'@'192.168.0.%' identified by 'yjscloud';严格的授权：重视安全，忽略方便
grant select,insert,update,delete on blog.* to 'blog'@'192.168.0.%' identified by 'yjscloud';4）生产环境从库（只读）用户的授权：
grant select on blog.* to 'blog'@'192.168.0.%' identified by 'yjscloud';说明：这里表示给192.168.0.0/24的用户blog管理blog数据的所有表（*表示所有表）只读权限（select），密码为yjscloud
5）生产场景授权的具体命令为：
grant select,insert,update,delete on blog.* to 'blog'@'192.168.0.%' identified by 'yjscloud';从库授权用户的命令：
grant select on blog.* to 'blog'@'192.168.0.%' identified by 'yjscloud';当然从库除了做select的授权外，还可以加入read-only等只读从参数，严格控制web用户写从库。
6）这里还有一个比较重要的问题：
就是主从库的mysql库和表示同步的，无法针对同一个用户授权不同的权限，因为主库授权后会自动同步到从库上，导致从库的授权只读失败。
解决方法：
a、取消mysql库的同步
b、授权主库权限后，从库执行收回增删改权限。
c、不在授权上控制增删改，而是用read-only参数，控制普通用户更新从库，注意，read-only参数对超级用户无效。
查看mysql数据库的用户及授权
1）查看mysql数据库的用户及授权
mysql> select user,host from mysql.user;
+----------+---------------------------+
| user | host |
+----------+---------------------------+
| blog | 192.168.0.% |
| yjscloud | 192.168.0.% |
| yjscloud | 192.168.0.0/255.255.255.0 |
| mycloud | localhost |
| root | localhost |
| system | localhost |
| yjscloud | localhost |
+----------+---------------------------+
7 rows in set (0.00 sec)注意：
1、由于安全原因，上面的用户是经过处理过的
2、mysql的用户由“用户名@主机名”构成，所以在用户列表有相同的用户，不要奇怪
2）查看授权用户yjscloud的具体授权权限
mysql> show grants for 'yjscloud'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for yjscloud@localhost |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'yjscloud'@'localhost' IDENTIFIED BY PASSWORD '*7C9FFD5B56F6DEE1126BC972C97B1DFEC8E4E089' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) mysql数据库操作
指定字符集建库
指定字符集建库
mysql> create database yjscloud;
Query OK, 1 row affected (0.00 sec)
mysql> show create database yjscloud\G
*************************** 1. row ***************************
Database: yjscloud
Create Database: CREATE DATABASE yjscloud /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)建表
基本语法：
create table <表名>（
<字段名1><类型1>，
...
<字段名n><类型n>，提示：其中create table 是关键字，不能更改，但是大小写可以变化。
建表语句：下面是人工写法设计的建表语句例子，表名student，
create table student(
id int(4) not null,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default null
);第二种mysql生成的建表语句student表例子
CREATE TABLE student ( #create table表示创建表的固定关键字，student为表名
id int(4) NOT NULL, #学号列，数字类型，长度为4，不为空值
name char(20) NOT NULL, #名字列，定长字符类型，长度20，不为空值
age tinyint(2) NOT NULL DEFAULT '0', #年龄列，很小的数字类型，长度为2，不为空，默认为0值，
dept varchar(16) DEFAULT NULL #系别列，变长字符类型，长度16，默认为空
) ENGINE=InnoDB DEFAULT CHARSET=utf8 #引擎和字符集，引擎默认为InnoDB,字符集继承库的utf8实战操作
下面是实战操作
mysql> create database test_stu;
Query OK, 1 row affected (0.00 sec)
mysql> show create database test_stu\G
*************************** 1. row ***************************
Database: test_stu
Create Database: CREATE DATABASE test_stu /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
mysql> use test_stu;
Database changed
mysql> create table student(
-> id int(4) not null,
-> name char(20) not null,
-> age tinyint(2) not null default '0',
-> dept varchar(16) default null
-> );
Query OK, 0 rows affected (0.02 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,
age tinyint(2) NOT NULL DEFAULT '0',
dept varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>查看表结构：
mysql表的字段类型
1）数字类型
2）日期和时间类型（DATE日期类型：支持的范围是1000-01-01到9999-12-31。mysql以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列）
3）串类型
char和varchar之间的差别
m
生产环境标准的UTF8G格式表结构语句
某sns生产正式建表语句
use sns;
set names gbk;
CREATE TABLE subject_comment_manager (
subject_comment_manager_id bigint(12) NOT NULL auto_increment COMMENT '主键',
subject_type tinyint(2) NOT NULL COMMENT '素材类型',
subject_primary_key varchar(255) NOT NULL COMMENT '素材的主键',
subject_title varchar(255) NOT NULL COMMENT '素材的名称',
edit_user_nick varchar(64) default NULL COMMENT '修改人',
edit_user_time timestamp NULL default NULL COMMENT '修改时间',
edit_comment varchar(255) default NULL COMMENT '修改的理由',
state tinyint(1) NOT NULL default '1' COMMENT '0代表关闭，1代表正常',
PRIMARY KEY (subject_comment_manager_id),
KEY IDX_PRIMARYKEY (subject_primary_key(32)), #<==括号内的32表示对前32个字符做前缀索引。
KEY IDX_SUBJECT_TITLE (subject_title(32))
KEY index_nick_type (edit_user_nick(32),subject_type)#<==联合索引，此行为新加的，用于给大家讲解的。实际表语句内没有此行。
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 查看建立的表结构
查看表结构命令：
desc表名或者show columns from 表名例：
查看已建立表的语句
查看建表的语句（可以看见索引及创建表的相关信息）：
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,
age tinyint(2) NOT NULL DEFAULT '0',
dept varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec) 为表的字段创建索引
索引就象书的目录一样，如果在字段上建立了索引，那么以索引为查询条件时可以加快查询数据的速度.创建主键索引查询数据库，按主键查询是最快的，每个表只有一个主键列，但是可以用多个普通索引列。主键列要求列的所有内容必须唯一，而普通索引不要求内容必须唯一。主键就类似我们在学校学习时的学号一样，班级内是唯一的，整个表的每一条记录的主键值在表内都是唯一的，用来唯一标识一条记录。
建立主键索引的方法
1）在建表时，可以增加建立主键索引的语句如下
create table student(
id int(4) not null AUTO_INCREMENT,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL,
primary key(id),
KEY index_name(name)
); 提示：
primary key(id) #主键
KEY index_name(name) #name字段普通索引
建表时附带建立主键语句操作演示：
提示：PRI为主键的标示，MUL为普通索引的标示
建立后通过alter命令增加主键索引（不这么干）
1）主键列不能重复创建，必须先删除上面配置
利用alter命令修改id列为自增主键列
alter table student change id id int primary key auto_increment;提示：只有int类型且为primary key才可以使用auto_increment
对字段的前N个字符创建普通索引
当遇到表中比较大的列时，列内容的前N个字符在所有内容中已经接近唯一时，这时可以对列的前N个字符建立索引，而无需对整个列建立索引，这样可以节省创建索引占用的系统空间，以及降低读取和更新维护索引消耗的系统资源。对字段的前N个字符创建普通索引的语法：
create index index_name on test(name(8)); #条件列前N个字符创建所索引操作实践：
在dept系列上，前8个字符创建索引，此列总长度为16个
为指定字段的前N个字符建立索引语句
mysql> create index index_dept on student(dept(8));
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0这个语句等同于alter table student add index index_dept(dept(8));
mysql> alter table student drop index index_dept;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table student add index index_dept(dept(8));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0查看执行结果：
为表的多个字段创建联合索引
如果查询数据的条件是多列时，我们可以为多个查询的列创建联合索引，甚至可以为多列的前N个字符创建联合索引。
实践演示：
可以为多列的前N个字符列创建联合索引，实践演示：
mysql> create index ind_name_dept1 on student(name(8),dept(10));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0创建唯一索引（非主键）
create unique index index_age on student(age);
索引列的创建及生效条件
问题1、既然索引可以加快查询速度，那么就给所有的列建索引吧？
答：因为索引不但占用系统空间，更新数据库是还需要维护索引数据的，因此索引是一把双刃剑，并不是越多越好，
例如：数十到几百行的小表上无需建立索引，更新频繁，读取比较少的表 要少建立索引。
问题2、需要在哪些列上创建索引呢？
select user,host from mysql where host=...，索引一定要创建在where后的条件列上，
而不是select后的选择数据的列。另外，我们要尽量选择在唯一值多的大表上的列建立索引。
创建索引命令集合小结
1）创建索引相关命令集合
创建主键索引：
alter table student chage id id int primary key auto_increment;删除主键索引（主键列不能自增）：
alter table student drop primary key;添加普通索引：
alter table student add index index_dept(dept);根据的前n个字符创建索引：
create index index_dept on student(dept(8));根据多个列创建联合索引：
create index index_name_dept on student(name,dept);创建唯一索引：
create unique index uni_ind_name on student(name);删除普通索引与唯一索引：
alter table student drop index index_dept;
drop index index_dept on student;基本索引：
1、要在表的列上创建索引
2、索引会加快查询速度，但是会影响更新的速度，因此要维护索引。
3、索引不是越多越好，要在频繁查询的where后的条件列上创建索引。
4、小表或唯一值极少的列上不建索引，要在大表以及不同内容多的列上创建索引。
往表中插入数据
1）命令语法：
insert into <表名>[(<字段名>[,..<字段名n>])] values (值 1 )[,(值n)]2）新建一个简单的测试表test
3）往表中插入数据的不同的语法例子：
1、按规矩指定所有列名，并且每列都插入值
insert into test1(id,name) values(1,'xwq');2、由于id列为自增的，所以，可以只在name列插入值
insert int test1(name) values('xdf')3、如果不指定列，就要按规矩为每列都插入恰当的值。
insert into test1 values(3,'inca',21);4、批量插入数据方法，提升效率
insert into test1 values(3,'lufei',20),(4,'suolong',23);4）插入数据实践演示：
插入数据：
mysql> insert into test1(id,name) values(1,'xwq');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test1 values(2,"lufei",12);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(3,'inca',21);
Query OK, 1 row affected (0.01 sec)
5）测试完毕，退出数据库然后备份上述数据库，留着备用：
mysql> exit
Bye
[root@mysql-1 ~]# mysqldump -uroot -pyjscloud000 -B test_yjs >/opt/test_yjs_bak.sql
[root@mysql-1 ~]# ls -l /opt/test_yjs_bak.sql
-rw-r--r-- 1 root root 2014 May 11 21:39 /opt/test_yjs_bak.sql6）备份后检查备份的sql数据内容：过滤无用信息
grep -E -v "#|\/|^$|--" /opt/test_yjs_bak.sql
查询数据
语法
select <字段1,字段2，…> from <表名> where <表达式>其中，select,from,where是不能随便改的，是关键字，支持大小写
查看表test1中所有数据
1）进入指定库后查询
查看mysql库的用户
根据指定条件查询表的部分数据
1）例：查看表test1中第一行数据
2）指定固定条件查询数据
注意：字符类型的查询条件的值要带引号
3）指定固定条件范围查询数据
4）其他查询功能
排序功能：
多表查询
建立几个关联表
要实现多表连表查询，就需要关联表及数据，表名及字段内容如下：
学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
-------(学号-主键，姓名，性别，年龄，所在系）
课程表：Course(Cno,Cname,Cpno,Ccredit)
-------(课程号-主键，课程名，选课，学分)
选课表：SC(Sno,Cno,Grade)
-------(学号-主键，课程号-主键，成绩)创建关联表语句
1）创建学生表
drop table student;
create table student(
Sno int(10) NOT NULL COMMENT '学号',
Sname varchar(16) NOT NULL COMMENT '姓名',
Ssex char(2) NOT NULL COMMENT '性别',
Sage tinyint(2) NOT NULL default '0' COMMENT '学生年龄',
Sdept varchar(16) default NULL COMMENT '学生所在系别',
PRIMARY KEY (Sno) ,
key index_Sname (Sname)
);2）创建课程表
create table course(
Cno int(10) NOT NULL COMMENT '课程号',
Cname varchar(64) NOT NULL COMMENT '课程名',
Ccredit tinyint(2) NOT NULL COMMENT '学分',
PRIMARY KEY (Cno)
);3）创建选课表
CREATE TABLE SC (
SCid int(12) NOT NULL auto_increment COMMENT '主键',
Cno int(10) NOT NULL COMMENT '课程号',
Sno int(10) NOT NULL COMMENT '学号',
Grade tinyint(2) NOT NULL COMMENT '学生成绩',
PRIMARY KEY (SCid)
);往关联表中填充数据
1）学生表插入数据：
INSERT INTO student values(0001,'路飞','男',17,'计算机网络');
INSERT INTO student values(0002,'索隆','男',19,'网络安全');
INSERT INTO student values(0003,'乌索普','男',18,'网络媒体');
INSERT INTO student values(0004,'娜美','女',17,'旅游管理');
INSERT INTO student values(0005,'香吉士','男',22,'新东方厨校');
INSERT INTO student values(0006,'妮可罗宾','女',28,'古代历史');2）课程表插入数据
INSERT INTO course values(1001,'Linux中高级运维',3);
INSERT INTO course values(1002,'Linux高级架构师',5);
INSERT INTO course values(1003,'MySQL高级Dba',4);
INSERT INTO course values(1004,'Python运维开发',4);
INSERT INTO course values(1005,'Java web开发',3);3）选课表插入数据
INSERT INTO SC(Sno,Cno,Grade) values(0001,1001,4);
INSERT INTO SC(Sno,Cno,Grade) values(0001,1002,3);
INSERT INTO SC(Sno,Cno,Grade) values(0001,1003,1);
INSERT INTO SC(Sno,Cno,Grade) values(0001,1004,6);
INSERT INTO SC(Sno,Cno,Grade) values(0002,1001,3);
INSERT INTO SC(Sno,Cno,Grade) values(0002,1002,2);
INSERT INTO SC(Sno,Cno,Grade) values(0002,1003,2);
INSERT INTO SC(Sno,Cno,Grade) values(0002,1004,8);
INSERT INTO SC(Sno,Cno,Grade) values(0003,1001,4);
INSERT INTO SC(Sno,Cno,Grade) values(0003,1002,4);
INSERT INTO SC(Sno,Cno,Grade) values(0003,1003,2);
INSERT INTO SC(Sno,Cno,Grade) values(0003,1004,8);
INSERT INTO SC(Sno,Cno,Grade) values(0004,1001,1);
INSERT INTO SC(Sno,Cno,Grade) values(0004,1002,1);
INSERT INTO SC(Sno,Cno,Grade) values(0004,1003,2);
INSERT INTO SC(Sno,Cno,Grade) values(0004,1004,3);
INSERT INTO SC(Sno,Cno,Grade) values(0005,1001,5);
INSERT INTO SC(Sno,Cno,Grade) values(0005,1002,3);
INSERT INTO SC(Sno,Cno,Grade) values(0005,1003,2);
INSERT INTO SC(Sno,Cno,Grade) values(0005,1004,9);查询SC表结果：
连表查询：
select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno;
查询语句执行计划
使用explain查询select查询语句执行计划，即使用索引情况
修改表中数据
修改表中指定条件固定列的数据
1）命令语法：
update 表名 set 字段=新值，... where 条件2）修改指定的行字段内容
1、查看要修改的表
2、修改id为3的行的名字为xiangjishi
修改表中所有行的数据
为了演示效果我们往多插入几行数据
INSERT INTO test1 values(1,'wusuopu',19);
INSERT INTO test1 values(2,'namei',18);
INSERT INTO test1 values(5,'lubing',28);
1、不带条件更改所有表的记录
update一个记录，结果忘了加where，于是悲剧发生了！
防止误操作mysql数据库的例子
1、mysql帮助说明
2、指定-U登录测试
提示：不加条件无法删除，目的达到。
3、做成别名防止DBA误操作
结论：在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行
删除表中数据
实践删除表中的数据
1）语法命令：
delete from 表名 where 表达式1、实践，例如：删除表test1 中name等于lufei的记录
命令：
truncate table 表名
truncate table test1; #清空表中所有内容
truncate table test1;和delete from test1;区别
1、truncate table test1;更快。清空物理文件
2、delete from test1;逻辑清除，按行删。
增删改表的字段
命令语法及默认添加演示
1）命令语法：
alter table 表名 add 字段 类型 其他；2）测试表数据
3）实践案例
例：在表test1中添加字段sex，age，qq，类型分别为char(4),int(4),varchar(15)
4）执行的命令演示
1、添加性别列，默认语句
alter table test1 add sex char(4);
提示：默认增加列增加到所有字段的结尾
指定添加列在表里的位置
1、定添加年龄列到name列后面的位置
alter table test1 add age int(4) after name; #在name列后增加age列2、在第一列添加qq字段
alter table test1 add qq varchar(15) first;
生产案例
生产环境多个复杂添加修改多字段信息的案例
1）增加1个字段：
alter table 'etiantian' add 'firstphoto_url' varchar(255) default null comment '第一张图片url';2）增2个字段：
alter table 'basic' add 'adtml_top' varchar(1024) default null comment '顶部广告html', and 'adtml_right' varchar(1024) default null comment '右侧广告html';3）修改字段类型：
4）修改字段名称
mysql的重命名
命令语法：
rename table 原表名 to 新表名；例如： 在表test1名字更改为 test1_change
执行结果：
alter法
删除表
命令语法：
drop table <表名>例如：删除表名为test的表
执行结果：
作者：废权
链接：https://blog.yjscloud.com/archives/68
声明：如无特别声明本文即为原创文章仅代表个人观点，版权归《废权的博客》所有，欢迎转载，转载请保留原文链接。
共有 0 条评论