mysql-5.7.20基本用法
第1章 安装mysql-5.7.20
1.1 系统环境
[root@mysql ~]# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
[root@mysql ~]# una
unalias uname
[root@mysql ~]# uname -r
3.10.0-327.el7.x86_64
[root@mysql ~]# getenforce
Disabled
[root@mysql ~]# systemctl status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
卸载自带mariadb
[root@mysql mysql]# rpm -qa|grep mariadb
mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@mysql mysql]# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@mysql mysql]# rpm -qa|grep mariadb
1.2 下载软件（二进制包）
[root@mysql ~]# mkdir /tar
[root@mysql tar]# mkdir /app/mysql-5.7.20 -p
[root@mysql ~]# wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar
#可以查找自己想要的版本。此处用的是中国科学技术大学的源。
解压到指定目录
[root@mysql tar]# tar zxf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@mysql tar]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /app/mysql-5.7.20
做软链接
[root@mysql tar]# ln -s /app/mysql-5.7.20/ /app/mysql
添加mysql用户，授权
[root@mysql tar]# useradd -M -s /sbin/nologin mysql
[root@mysql tar]# chown -R mysql.mysql /app/mysql-5.7.20/
mysql初始化
[root@mysql ~]# /app/mysql/bin/mysqld --initialize --user=mysql --basedir=/app/mysql --datadir=/app/mysql/data
2018-08-09T19:21:34.385804Z 1 [Note] A temporary password is generated for root@localhost: /gevck>>s07F
初始化密码
将mysql放到本地系统服务中
[root@mysql ~]# cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
如果安装到/usr/local目录下就省去以下操作
sed -i 's#/usr/local#/app#g' /app/mysql-5.7.20/bin/mysqld_safe /etc/init.d/mysqld
启动
[root@mysql ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
添加环境变量
[root@mysql ~]# tail -1 /etc/profile
PATH=$PATH:/app/mysql/bin
[root@mysql ~]# source /etc/profile
修改密码
用初始密码登录
mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
修改密码
[root@mysql ~]# mysqladmin -uroot -p123456 password 123
第2章 基本操作
2.1 用户管理
权限详解
'10.0.0.200' ---->只允许200地址访问我mysql
'10.0.0.%' -----》允许这个网段的所有服务器都能访问我
'10.0.0.5%' ----》 允许50-59
'%' ----> 允许所有人
2.1.1 创建用户
mysql> create user name2@'%' identified by "123";
mysql> create user test1@'%' identified by "123";
mysql> select user,host from mysql.user;
+---------------+------------+
| user | host |
+---------------+------------+
| name2 | % |
| test1 | % |
2.1.2 删除用户
mysql> select user,host from mysql.user;
mysql> select user,host from mysql.user;
+---------------+------------+
| user | host |
+---------------+------------+
| name2 | % |
| name1 | 172.16.1.% |
2.2 权限管理
2.2.1 查看当前用户和数据库
mysql> select user();
查看当前用户
mysql> select database();
查看当前数据库
mysql> grant all on *.* to name2@'%' identified by '123';
2.2.2 授权
给name2所有库所有表的权限
mysql> show grants for name2;
+--------------------------------------------+
| Grants for name2@% |
+--------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'name2'@'%' |
+--------------------------------------------+
mysql> grant all on *.* to root@'localhost' identified by '123456';
mysql> grant all on *.* to root@'10.0.0.%' identified by '123456';
mysql> grant all on *.* to root@'%' identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user.host from mysql.user where user like 'root';
+-----------+
| host |
+-----------+
| % |
| 10.0.0.% |
| localhost |
2.2.3 创建用户的同时授权
mysql> grant all on *.* to name3@'%' identified by "123";
mysql> select user,host from mysql.user;
+---------------+------------+
| user | host |
+---------------+------------+
| name2 | % |
| name3 | % |
2.2.4 授权用户name3 select,create权限
mysql> grant select,create on *.* to name3@'%' identified by "123";
2.2.5 回收权限
mysql> revoke update,delete on *.* from name3@'%';
mysql> revoke all on *.* from name3@'%';
2.2.6 创建库database 带字符编码
创建database
mysql> create database test1 charset utf8;
mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
2.3 基础知识
2.3.1 接口命令
\h 或 help 或 ? 获取帮助
\G 格式化输出（行转列）
\T 或 tee 记录操作日志 tee /tmp/mysql.log
\c 或 CTRL+d 退出mysql
\s 或 status 查看数据库状态信息
\. 或 source mysql> source /tmp/world.sql
\! 使用shell中的命令 mysql> \! cat /etc/redhat-release CentOS release 6.9 (Final)
2.3.2 mysqladmin用法
mysqladmin -u用户 -p密码 ping “强制回应 (Ping)”服务器。
mysqladmin -u用户 -p密码 shutdown 关闭服务器。
mysqladmin -u用户 -p密码 create databasename 创建数据库。
mysqladmin -u用户 -p密码drop databasename 删除数据库
mysqladmin -u用户 -p密码 version 显示服务器和版本信息
mysqladmin -u用户 -p密码 status 显示或重置服务器状态变量
mysqladmin -u用户 -p密码 password 设置口令
mysqladmin -u用户 -p密码 flush-privileges 重新刷新授权表。
mysqladmin -u用户 -p密码 flush-logs 刷新日志文件和高速缓存。
第3章 基本语句
3.1 创建数据库
mysql> create database test charset utf8;
3.2 修改存在的字符编码
mysql> alter database test charset gbk;
3.3 查看支持的字符集和校对规则
mysql> show character set;
3.4 切换数据库查看表
mysql> use world;
Database changed
mysql> show tables;
3.5 查看当前登录用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
3.6 创建表
mysql> create table t1(id int,name char(30),sex char(4));
id号码 名字 性别
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(30) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3.7 查看创建表语句
mysql> show create table t1;
| Table | Create Table
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(30) DEFAULT NULL,
`sex` char(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
3.8 修改表的相关信息
3.8.1 修改表的名字
mysql> rename table t1 to t2;
Query OK, 0 rows affected (0.00 sec)
方法二。
mysql> alter table t2 rename to t1;
Query OK, 0 rows affected (0.00 sec)
3.8.2 修改表结构
添加地址一列到table t1中非
mysql> alter table t1 add addr char(40) not null;
| id | int(11) | YES | |空
mysql> desc t1； NULL | |
| name | char(30) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| addr | char(40) | NO | | NULL | |
3.8.3 指定添加年龄列到name列后面的位置
mysql> alter table t1 add age int(4) after name;
mysql> desc t1;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | YES | | NULL | |
| name | char(30) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
3.8.4 在第一列添加WeChat字段
mysql> alter table t1 add wechat int(20) first;
mysql> desc t1;
| Field | Type | Null | Key | Default | Extra |
| wechat | int(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
3.8.5 复制一个表格
mysql> create table t2 select * from t1;
mysql> create table t3 like t1; 只是复制表结构。
3.8.6 同时添加多个列定义
mysql> alter table t1 add pro char(40), add qq int;
mysql> desc t1;
| Field | Type | Null | Key | Default | Extra |
| wechat | int(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | char(30) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| addr | char(40) | NO | | NULL | |
3.8.7 删除表结构
mysql> alter table t1 drop addr;
3.8.8 修改表定义
mysql> alter table t1 modify wechat int(30);
mysql> desc t1;
| Field | Type | Null | Key | Default | Extra |
| wechat | int(30) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
3.8.9 修改列名
mysql> alter table t1 change name test_name char(30);
mysql> desc t1;
| Field | Type | Null | Key | Default | Extra |
| wechat | int(30) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| test_name | char(30) | YES | | NULL | |
3.9 DML数据操作
3.9.1 insert
mysql> desc t1;
| Field | Type | Null | Key | Default | Extra |
| wechat | int(30) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| test_name | char(30) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| pro | char(40) | YES | | NULL | |
| qq | int(11) | YES | | NULL | |
mysql> select * from t1;
| wechat | id | test_name | age | sex | pro | qq |
| NULL | 1 | NULL | NULL | NULL | NULL | NULL |
mysql> insert into t1 values(123,2,'limit2',20,'man','teacher',123);
mysql> select * from t1;
| wechat | id | test_name | age | sex | pro | qq |
| NULL | 1 | NULL | NULL | NULL | NULL | NULL |
| 123 | 2 | limit2 | 20 | man | teacher | 123 |
在WeChat下面插入一个微信号
mysql> insert into t1(wechat)values(789);
mysql> select * from t1;
| wechat | id | test_name | age | sex | pro | qq |
| NULL | 1 | NULL | NULL | NULL | NULL | NULL |
| 123 | 2 | limit2 | 20 | man | teacher | 123 |
| 456 | 3 | limit3 | 20 | man | docter | 456 |
| 789 | NULL | NULL | NULL | NULL | NULL | NULL |
3.9.2 插入多行数据
mysql> insert into t1 values(111,2,'limit4',21,'women','nurse',123),(112,2,'limit5',22,'man','woker',123),(113,2,'limit6',23,'man','SE',123);
mysql> select * from t1;
| wechat | id | test_name | age | sex | pro | qq |
| NULL | 1 | NULL | NULL | NULL | NULL | NULL |
| 123 | 2 | limit2 | 20 | man | teacher | 123 |
| 456 | 3 | limit3 | 20 | man | docter | 456 |
| 789 | NULL | NULL | NULL | NULL | NULL | NULL |
| 111 | 2 | limit4 | 21 | women | nurse | 123 |
| 112 | 2 | limit5 | 22 | man | woker | 123 |
| 113 | 2 | limit6 | 23 | man | SE | 123 |
3.9.3 更改表内容（要加上where条件）
mysql> update t1 set test_name='limit7' where test_name='limit6';
mysql> select * from t1;
| 113 | 2 | limit7 | 23 | man | SE | 123 |
3.9.4 删除表内容
mysql> select * from t1;
| wechat | id | test_name | age | sex | pro | qq |
| NULL | 1 | NULL | NULL | NULL | NULL | NULL |
| 123 | 2 | limit2 | 20 | man | teacher | 123 |
| 456 | 3 | limit3 | 20 | man | docter | 456 |
| 789 | NULL | NULL | NULL | NULL | NULL | NULL |
| 111 | 2 | limit4 | 21 | women | nurse | 123 |
| 112 | 2 | limit5 | 22 | man | woker | 123 |
| 113 | 2 | limit7 | 23 | man | SE | 123 |
删除WeChat=798一行
mysql> delete from t1 where wehchat='789'; 必须加上where条件
mysql> select * from t1;
| wechat | id | test_name | age | sex | pro | qq |
| NULL | 1 | NULL | NULL | NULL | NULL | NULL |
| 123 | 2 | limit2 | 20 | man | teacher | 123 |
| 456 | 3 | limit3 | 20 | man | docter | 456 |
| 111 | 2 | limit4 | 21 | women | nurse | 123 |
| 112 | 2 | limit5 | 22 | man | woker | 123 |
| 113 | 2 | limit7 | 23 | man | SE | 123 |
================================================================================
mysql> select * from t1;
| wechat | id | test_name | age | sex | pro | qq |
| 123 | 2 | limit2 | 20 | man | teacher | 123 |
| 456 | 3 | limit3 | 20 | man | docter | 456 |
| 111 | 2 | limit4 | 21 | women | nurse | 123 |
| 112 | 2 | limit5 | 22 | man | woker | 123 |
| 113 | 2 | limit7 | 23 | man | SE | 123 |
删除id=2的数据
mysql> delete from t1 where id=2;
mysql> select * from t1;
| wechat | id | test_name | age | sex | pro | qq |
| 456 | 3 | limit3 | 20 | man | docter | 456 |
================================================================================================
truncate table test; #物理删除，pages(block)，效率高。
3.10 DQL数据查询语言标准语法
3.10.1 查看用户连接信息
mysql> select user,host,authentication_string from mysql.user;
mysql5.7之后password改为了authentication_string
| user | host | authentication_string |
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| name1 | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| name2 | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
3.10.2 查看所有信息关于test库下t1表格
mysql> select * from test.t1;
| wechat | id | test_name | age | sex | pro | qq |
| NULL | 1 | NULL | NULL | NULL | NULL | NULL |
| 123 | 2 | limit2 | 20 | man | teacher | 123 |
| 456 | 3 | limit3 | 20 | man | docter | 456 |
| 789 | NULL | NULL | NULL | NULL | NULL | NULL |
| 111 | 2 | limit4 | 21 | women | nurse | 123 |
| 112 | 2 | limit5 | 22 | man | woker | 123 |
| 113 | 2 | limit6 | 23 | man | SE | 123 |
3.10.3 查找年龄为21的人的id和WeChat号码
mysql> select wechat,id from test.t1 where age=21;
| wechat | id |
| 111 | 2 |
3.10.4 查找年龄大于21的人的id和WeChat号码
mysql> select wechat,id from test.t1 where age>21;
| wechat | id |
| 112 | 2 |
| 113 | 2 |
3.10.5 or和and
mysql> select wechat,id from test.t1 where age>21 and wechat>112;
| wechat | id |
| 113 | 2 |
mysql> select wechat,id from test.t1 where age>21 or wechat>112;
| wechat | id |
| 123 | 2 |
| 456 | 3 |
| 789 | NULL |
| 112 | 2 |
| 113 | 2 |
3.10.6 排序
mysql> select wechat,id,test_name from test.t1 order by id asc; 小到大
| wechat | id | test_name |
| 789 | NULL | NULL |
| NULL | 1 | NULL |
| 123 | 2 | limit2 |
| 111 | 2 | limit4 |
| 112 | 2 | limit5 |
| 113 | 2 | limit6 |
| 456 | 3 | limit3 |
7 rows in set (0.00 sec)
mysql> select wechat,id,test_name from test.t1 order by id desc; 大到小
| wechat | id | test_name |
| 456 | 3 | limit3 |
| 123 | 2 | limit2 |
| 111 | 2 | limit4 |
| 112 | 2 | limit5 |
| 113 | 2 | limit6 |
| NULL | 1 | NULL |
| 789 | NULL | NULL |
3.10.7 显示第二行后的六行（这里着五行了所有全部显示粗来）和desc和asc
mysql> select wechat,id,test_name from test.t1 order by id desc limit 2,6;
| wechat | id | test_name |
| 111 | 2 | limit4 |
| 112 | 2 | limit5 |
| 113 | 2 | limit6 |
| NULL | 1 | NULL |
| 789 | NULL | NULL |
3.11 安全操作设置（防止不加where删除过多的数据）
[root@mysql ~]# mysql -uroot -p123
mysql> select * from t3;
| wechat | id | test_name | age | sex | pro | qq |
| NULL | 1 | NULL | NULL | NULL | NULL | NULL |
| 123 | 2 | limit2 | 20 | man | teacher | 123 |
| 456 | 3 | limit3 | 20 | man | docter | 456 |
| 789 | NULL | NULL | NULL | NULL | NULL | NULL |
| 111 | 2 | limit4 | 21 | women | nurse | 123 |
| 112 | 2 | limit5 | 22 | man | woker | 123 |
| 113 | 2 | limit6 | 23 | man | SE | 123 |
mysql> delete from t3;
mysql> select * from t3;
Empty set (0.00 sec)
整个表格的内容都没了
==============================================================================================
登录的时候加上“-U”参数
[root@mysql ~]# mysql -uroot -p123 -U
mysql> select * from test.t2;
| wechat | id | test_name | age | sex | pro | qq |
| NULL | 1 | NULL | NULL | NULL | NULL | NULL |
| 123 | 2 | limit2 | 20 | man | teacher | 123 |
| 456 | 3 | limit3 | 20 | man | docter | 456 |
| 789 | NULL | NULL | NULL | NULL | NULL | NULL |
| 111 | 2 | limit4 | 21 | women | nurse | 123 |
| 112 | 2 | limit5 | 22 | man | woker | 123 |
| 113 | 2 | limit6 | 23 | man | SE | 123 |
7 rows in set (0.00 sec)
mysql> delete from test.t2;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
提示你使用了一个安全的update设置，你没用加上where使用。
为了安全设置mysql-U为别名
[root@mysql ~]# echo "alias mysql='mysql -U'" >> /etc/profile
[root@mysql ~]# source /etc/profile
[root@mysql ~]# mysql -uroot -p123
mysql> delete from test.t2;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
第4章 数据类型
4.1.1 数值数据类型
整数 TINYINT 极小整数数据类型（0-255）
整数 SMALLINT 较小整数数据类型（-2^15 到2^15-1）
整数 MEDIUMINT 中型整数数据类型
整数 INT常规（平均） 大小的整数数据类型（-2^31 到2^31-1）
整数 BIGINT 较大整数数据类型（-2^63到2^63-1）
浮点数 FLOAT 小型单精度（四个字节）浮点数
浮点数 DOUBLE 常规双精度（八个字节）浮点数
定点数 DECIMAL 包含整数部分、小数部分或同时包括二者的精确值数值
4.1.2 字符串数据类型
文本 CHAR 固定长度字符串，最多为255 个字符
文本 VARCHAR 可变长度字符串，最多为65,535 个字符
文本 TINYTEXT 可变长度字符串，最多为255 个字符
文本 TEXT 可变长度字符串，最多为65,535 个字符
文本 MEDIUMTEXT 可变长度字符串，最多为16,777,215 个字符
文本 LONGTEXT 可变长度字符串，最多为4,294,967,295 个字符
整数 ENUM 由一组固定的合法值组成的枚举
整数 SET 由一组固定的合法值组成的集
4.1.3 二进制数据类型
二进制 BINARY类似于 CHAR（固定长度）类型， 但存储的是二进制字节字符串，
二进制 VARBINARY类似于 VARCHAR（可变长度）类型， 但存储的是二进制字节字符串，
BLOB TINYBLOB 最大长度为255 个字节的 BLOB 列
BLOB BLOB 最大长度为65,535 个字节的 BLOB 列
BLOB MEDIUDMBLOB 最大长度为16,777,215 个字节的 BLOB 列
BLOB LONGBLOB 最大长度为4,294,967,295 个字节的 BLOB 列
4.1.4 时间数据类型
DATE YYYY-MM-DD2 017-12-16
TIME hh:mm:ss[.uuuuuu] 12:59:02.123456
DATE TIMEYYYY-MM-DD hh:mm:ss[.uuuuuu]2017-12-16 12:59:02.123
TIME STAMPYYYY-MM-DD hh:mm:ss[.uuuuuu]2017-12-16 12:59:02.12
YEAR YYYY 2017
4.1.5 列属性
数值 UNSIGNED 禁止使用负值
仅整数 AUTO_INCREMENT 生成包含连续唯一整数值的序列
字符串 CHARACTER SET 指定要使用的字符集
字符串 COLLATE 指定字符集整理
字符串 BINARY 指定二进制整理
全部* NULL 或 NOT NULL 指示列是否可以包含 NULL 值
全部 DEFAULT 如果未为新记录指定值，则为其提供默认值
第5章 导入练习文件world
导入练习文件
官网
https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html
官网导入方法
https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html
[root@mysql ~]# ll world.sql
-rw-r--r-- 1 root root 397334 4月 2 22:58 world.sql
使用非交互式：(尽量避免使用mysql 导入数据，会产生大量的无用日志)
[root@mysql ~]# mysql -uroot -p123 </root/world.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| world |
使用Navicat或者SQLyog 链接 （前提要给用户授权才能链接）
5.1 select用法
逻辑 操作符说明
and逻辑与 只有当所有的子条件都为true时，and才返回true。否则返回false或null
or逻辑或 只要有一个子条件为true，or就返回true。否则返回false或null
not逻辑非 如果子条件为true，则返回false；如果子条件为false，则返回true
xor逻辑异 或当一个子条件为true而另一个子条件为false时，其结果为true；
当两个条件都为true或都为false时，结果为false。否则，结果为null
5.1.1 从数据库中查找是中国的并且是山西的城市
mysql> select * from world.city where countrycode='chn' and district='shanxi';
| ID | Name | CountryCode | District | Population |
| 1908 | Taiyuan | CHN | Shanxi | 1968400 |
| 1931 | Datong | CHN | Shanxi | 800000 |
| 1986 | Yangquan | CHN | Shanxi | 362268 |
| 2000 | Changzhi | CHN | Shanxi | 317144 |
| 2078 | Yuci | CHN | Shanxi | 191356 |
| 2083 | Linfen | CHN | Shanxi | 187309 |
| 2156 | Jincheng | CHN | Shanxi | 136396 |
| 2212 | Yuncheng | CHN | Shanxi | 108359 |
| 2233 | Xinzhou | CHN | Shanxi | 98667 |
5.1.2 查找一百万到一百零一万人口的城市（范围）
mysql> select * from city where population between 1000000 and 1010000;
| ID | Name | CountryCode | District | Population |
| 1466 | Napoli | ITA | Campania | 1002619 |
| 1786 | Amman | JOR | Amman | 1000000 |
| 2524 | Zapopan | MEX | Jalisco | 1002239 |
| 3591 | Perm | RUS | Perm | 1009700 |
5.1.3 查找id为1 3 5的城市（in的用法）
mysql> select * from city where id in (1,5,3);
| ID | Name | CountryCode | District | Population |
| 1 | Kabul | AFG | Kabol | 1780000 |
| 3 | Herat | AFG | Herat | 186800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
5.1.4 查找所有一jp开头的城市信息（like的用法）
mysql> select * from city where countrycode like 'jp%';
%匹配所有 _ 只表示一个任意字符
| ID | Name | CountryCode | District | Population |
| 1532 | Tokyo | JPN | Tokyo-to | 7980230 |
| 1533 | Jokohama [Yokohama] | JPN | Kanagawa | 3339594 |
| 1534 | Osaka | JPN | Osaka | 2595674 |
| 1535 | Nagoya | JPN | Aichi | 2154376 |
| 1536 | Sapporo | JPN | Hokkaido | 1790886 |
5.1.5 order by的用法
asc 执行升序排序。默认值
desc 执行降序排序
使用方法： ORDER BY子句一般在SELECT语句的最后面
在MySQL中，把NULL值当做一列值中的最小值对待。因此，升序排序时，它出现在最前面
mysql> select * from city where id in (1,8,9,31) order by population desc; 降序
| ID | Name | CountryCode | District | Population |
| 1 | Kabul | AFG | Kabol | 1780000 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
| 31 | Heerlen | NLD | Limburg | 95052 |
4 rows in set (0.01 sec)
mysql> select * from city where id in (1,8,9,31) order by population asc; 升序（默认是升序）
| ID | Name | CountryCode | District | Population |
| 31 | Heerlen | NLD | Limburg | 95052 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 1 | Kabul | AFG | Kabol | 1780000 |
mysql> select * from city where id in (1,8,9,31) order by population,countrycode;
以人口和国家排序
| ID | Name | CountryCode | District | Population |
| 31 | Heerlen | NLD | Limburg | 95052 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 1 | Kabul | AFG | Kabol | 1780000 |
mysql> select * from city where id in (1,8,9,31) order by 3;
以第三列国家排序
| ID | Name | CountryCode | District | Population |
| 1 | Kabul | AFG | Kabol | 1780000 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
| 31 | Heerlen | NLD | Limburg | 95052 |
5.1.6 limit用法
它是SELECT语句中的最后一个子句（在order by后面）。
它用来表示从结果集中选取最前面或最后面的几行。
mysql> select * from city where id in (1,8,9,31) order by 3 limit 2;
只是选出前两行。
| ID | Name | CountryCode | District | Population |
| 1 | Kabul | AFG | Kabol | 1780000 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
limit <获取的行数> [OFFSET <跳过的行数>]
或者
limit [<跳过的行数>,] <获取的行数>
mysql> select * from city where id in (1,8,9,31) order by 3 limit 2,2;
| ID | Name | CountryCode | District | Population |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
| 31 | Heerlen | NLD | Limburg | 95052 |
mysql> select * from city where id in (1,8,9,31) order by 3 limit 2 offset 1;
| ID | Name | CountryCode | District | Population |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
5.1.7 natural join
自动到两张表中查找所有同名同类型的列拿来做连接列，进行相等连接
使用natural join 进行相等连接，两个表，条件为人口大于1000000的，进行升序排列。
mysql> select name,id,name,countrycode,population,district from city natural join countrylanguage where popullation > 10000000 order by language;
5.1.8 using
mysql> select name,id,countrycode,population,language from city join countrylanguage
使用join进行两表的来连接，using指定countrycode为关联列。
using(countrycode);
| name | id | countrycode | population | language |
| Kabul | 1 | AFG | 1780000 | Balochi |
第6章 information_schema元数据
查询 INFORMATION_SCHEMA 数据库表。其中包含 MySQL 数据库服务器所管理的所有对象的相关数据
使用 SHOW 语句。用于获取数据库和表信息的 MySQL 专用语句
使用 DESCRIBE（或 DESC）语句。用于检查表结构和列属性的快捷方式
使用 mysqlshow 客户端程序。SHOW 语法的命令行程序
INFORMATION_SCHEMA 数据库优点介绍：
充当数据库元数据的中央系统信息库，模式和模式对象，服务器统计信息（状态变量、设置、连接） 。采用表格式以实现灵活访问，使用任意 SELECT 语句。是“虚拟数据库”，表并非“真实”表（基表），而是“系统视图”，根据当前用户的特权动态填充表。
6.1 INFORMATION_SCHEMA 数据库中所有的表
mysql> USE information_schema;
mysql> show tables;
| Tables_in_information_schema |
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
6.2 查找引擎是innodb的表
mysql> select table_name, engine from information_schema.tables where engine='innodb';
| table_name | engine |
| COLUMNS | InnoDB |
| t1 | InnoDB |
| t2 | InnoDB |
| city | InnoDB |
| country | InnoDB |
| countrylanguage | InnoDB |
6.3 查找数据类型是set的表
mysql> select table_schema,table_name, column_name from information_schema.columns where
data_type='set';
+--------------+--------------+-------------+
| table_schema | table_name | column_name |
+--------------+--------------+-------------+
| mysql | columns_priv | Column_priv |
| mysql | event | sql_mode |
| mysql | proc | sql_mode |
| mysql | procs_priv | Proc_priv |
6.4 查看找默认为yes的表
mysql> select character_set_name,collation_name,is_default from information_schema.collations where
is_default='yes';
6.5 查看每个数据库下表的个数
mysql> select table_schema,count(*) from information_schema.tables group by table_schema;
| table_schema | count(*) |
| information_schema | 61 |
| mysql | 31 |
| performance_schema | 87 |
| sys | 101 |
| test | 2 |
| world | 3 |
