linux12 -MYSQL数据库 --＞06数据库条件语句

SQL语句

一、SQL语句的语义

DDL: 数据定义语言
DCL: 数据控制语言
DML: 数据操作语言
DQL: 数据查询语言

DDL、DML、DQL、DCL之间的区别：

二、DDL语句 数据定义语言 (CREATE)

(Data Definition Language 数据定义语言）用于操作对象及对象本身，这种对象包括数据库,表对象，及视图对象。

包含的操作语句：

create:创建数据库和数据库的一些对象
drop:删除数据表、索引、触发程序、条件约束以及数据表的权限等
alter:修改数据表定义及数据属性

1.CREATE针对库的操作

1）查看语法

mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

2）创建库

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

mysql> create schema qiudao;
ERROR 1007 (HY000): Can't create database 'qiudao'; database exists

3）创建库时忽略已存在报错 [IF NOT EXISTS]

mysql> create schema lhd;
ERROR 1007 (HY000): Can't create database 'lhd'; database exists

mysql> create schema if not exists lhd;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> create schema if not exists lhd;
Query OK, 1 row affected, 1 warning (0.00 sec)

4）查看数据库创建

mysql> show create database qiudao;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| qiudao   | CREATE DATABASE `qiudao` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

5）指定字符集和校验规则创建数据库

mysql> create database qiudao charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database qiudao;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| qiudao   | CREATE DATABASE `qiudao` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

6）删除数据库

mysql> drop database qiudao;

7）修改数据库

mysql> alter database qiudao charset utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database qiudao;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| qiudao   | CREATE DATABASE `qiudao` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

2.CREATE针对表的操作

1）建表

#1.先进入数据库
mysql> use qiudao;
Database changed

#2.创建表，最少有一列
mysql> create table qiudao(id int);
Query OK, 0 rows affected (0.02 sec)

#3.查看表
mysql> show tables;
+------------------+
| Tables_in_qiudao |
+------------------+
| qiudao           |
+------------------+
1 row in set (0.00 sec)

2）数据类型

int:			整数  -2^31 ~ 2^31-1    (-2147483648 ~ 2147483647)
tinyint:		最小整数  -128 ~ 127
varchar:		字符类型（变长）
char:			字符类型（定长）
enum:			枚举类型  指定多个选项，选一个选项填写，不允许填写选项以外的值
datetime:		时间类型

3）建表数据属性

not null： 		非空
primary key： 	主键（唯一且非空的）
auto_increment： 自增（此列必须是：primary key或者unique key）
unique key： 	单独的唯一的
default： 		默认值
unsigned： 		无符号，非负数		#添加unsigned属性，会加到数据属性中，所以把这个属性写到数据属性后面
comment： 		注释

primary key = unique key + not null

4）加上建表属性创建学生表

#1.创建表
create table student(
sid int unsigned not null primary key auto_increment comment '学号',
sname varchar(10) not null comment '学生姓名',
sage tinyint unsigned not null comment '学生年龄',
sgender enum('m','f') not null default 'm' comment '学生性别',
scometime datetime default now() comment '入学时间',
sbirthday datetime comment '学生生日',
sclass varchar(20) comment '学生班级');

#2.查看建表语句
mysql> show create table student;

#3.插入语句
mysql> insert into student values(1,'邱导',88,'f',now(),now(),'初三1班');
Query OK, 1 row affected (0.00 sec)

mysql> insert student(sname,sage) values('哈',18);

#4.查看语句
mysql> select * from student;
+-----+--------+------+---------+---------------------+---------------------+------------+
| sid | sname  | sage | sgender | scometime           | sbirthday           | sclass     |
+-----+--------+------+---------+---------------------+---------------------+------------+
|   1 | 邱导   |   88 | f       | 2020-04-17 10:28:19 | 2020-04-17 10:28:19 | 初三1班    |
|   2 | 邱导   |   88 | f       | NULL                | NULL                | 初三1班    |
|   3 | 邱     |   74 | m       | NULL                | NULL                | 初三1班    |
|   4 | 林     |   18 | m       | NULL                | NULL                | 初四2班    |
+-----+--------+------+---------+---------------------+---------------------+------------+
4 rows in set (0.00 sec)

7）删除表

mysql> drop table student;

8）修改表

#1.修改表名
mysql> alter table linux7 rename linux7qi;
				  原表名          新表名

#2.在最后添加列
mysql> alter table qiudao add status int;

#3.添加多个列
mysql> alter table qiudao add status1 varchar(10),add status2 varchar(10);

#4.添加列到表前面
mysql> alter table qiudao add status3 varchar(10) first;

#5.指定位置添加列
mysql> alter table qiudao add status4 varchar(10) after status;

#6.删除指定列
mysql> alter table qiudao drop status;

#7.修改列的数据类型
mysql> alter table qiudao modify status varchar(10);

#8.修改列及属性
mysql> alter table qiudao change status4 status10 int;

三、DCL语句 数据控制语言（GRANT，REVOKE）

（Data Control Language 数据控制语句） 用于操作数据库对象的权限

• 包含的操作语句：

greate:分配权限给用户
revoke:废除数据库中某用户的权限

1.GRANT 授权

#1.授权语句
mysql> grant all on *.* to root@'%' identified by '123';

#2.查看用户权限
mysql> show grants for root@'%';

2.回收权限 revoke

#1.回收权限
mysql> revoke drop on *.* from root@'%';
Query OK, 0 rows affected (0.00 sec)

3.授权一个超级管理员

mysql> grant all on *.* to oldboy@'%' identified by '123' with grant option;

四、DML 数据操作语言 （INSERT，DELETE，UPDATE）

（Data Manipulation Language 数据操控语言) 用于操作数据库对象对象中包含的数据

• 包含的操作语句：

insert:向数据库插入一条数据
delete:删除表中的一条或多条记录
update:用于修改表中的数据

1.INSERT命令

#1.插入数据之前，一定先看表结构和建表规则
mysql> desc student;
mysql> show create table student;

#2.插入数据（不规范的写法）
mysql> insert into student values(6,'林',18,'m',NULL,NULL,NULL);

#3.插入一条数据（规范写法）
mysql> insert student(sname,sage) values('达',18);
mysql> insert student(sname,sage,sgender) values('丽',18,'f');

#4.插入多条数据（规范写法）
mysql> insert student(sname,sage,sgender) values('丽',18,'f'),('艺',18,'f'),('张音',18,'f');

扩展

#数据库修改某一列为唯一键的时候，那一列的数据不能有重复数据
#所以可以使用函数计算那一列是否有重复数据

#数据条数 类似于 wc -l
mysql> select count(name) from qiudao3;

#数据去重 类似于 uniq -c
mysql> select distinct(name) from qiudao3;

#去重之后计数，当值与没去重条数一致时可以为该列加唯一键
mysql> select count(distinct(name)) from qiudao3;

#city表name列不能做唯一键或主键
mysql> select count(name) from city;
mysql> select distinct(name) from city;

#country表name列能做唯一键或主键
mysql> select count(Name) from country;
mysql> select distinct(Name) from country;

2.update命令

#1.修改数据之前一点先查看数据
mysql> select * from student;
mysql> select * from qiudao.student;

#2.修改数据，错误的方法，这样会将整列都修改
mysql> update student set sgender'm';

#3.使用update一定要加where条件
mysql> update qiudao.student set sgender='f' where sid=11;
mysql> update qiudao.student set sgender='m' where sname='邱导' and sage='88';  #没有主键指点多个值

#4.如果要求整列修改
mysql> update student set sgender'm' where 1=1;

3.delete命令

#1.删除数据之前一点先查看数据
mysql> select * from student;
mysql> select * from qiudao.student;

#2.删除数据，错误的方法，会清空整个表
mysql> delete from qiudao.student;

#3.使用delete一定要加where条件
mysql> delete from qiudao.student where sid=1;
mysql> delete from qiudao.student where sname='林' and sage='18';

#4.清空表
truncate table student;

4.使用update代替delete

1）添加一列状态列

mysql> alter table student add status enum('1','0') default 1;

2）使用update修改数据状态

mysql> update student set status='0' where id=14;

3）查询有效的数据

mysql> select * from student where status=1;

五、DQL 数据查询语言（SELECT，desc）

(Data Query Language 数据查询语言 )用于查询数据

• 包含的操作语句：

select:用于查询表中的数据

1.select 查询数据

#1.查看表中所有数据，如果数据量太大，会导致内存撑爆
mysql> select * from qiudao.student;

#2.查询之前先查看下数据量
mysql> select count(*) from qiudao.student;

#3.查看指定列的数据
mysql> select user,host from mysql.user;

#4.按条件查询
mysql> select sname,sage from qiudao.student where sage=18;
mysql> select sname,sage from qiudao.student where sage=18 and sgender='f';

2.查询数据测试

#1.导入数据
mysql> \. /tmp/world.sql
或
mysql> source /tmp/world.sql

#2.查看表
mysql> use world
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

#3.查看city表结构
mysql> desc city;

#4.查询city表数据
mysql> select * from city;

#5.查询指定列数据
mysql> select name,population from city;

#6.按照人口数量排序
#升序
mysql> select name,population from city order by population;
#降序
mysql> select name,population from city order by population desc;

#7.只看前十条
mysql> select name,population from city limit 10;
mysql> select name,population from city order by population limit 10;

#8.按照步长查询数据
mysql> select id,name,population from city limit 10,10;
#前面的10代表开始，后面的10代表再次展示个数称为步长
#商品也翻页原理：
mysql> select id,name,population from city limit 0,60;
mysql> select id,name,population from city limit 60,60;
mysql> select id,name,population from city limit 120,60;

3.按条件查询

#1.条件查询where可以接的符号
where接条件符号：= < > >= <= != <>
where接条件：or and like
= : 精确查询
< > >= <= != <> : 范围查询
like : 模糊查询

#2.查询中国城市人口
mysql> select name,population from city where CountryCode='CHN';

#3.查询黑龙江省的人口
mysql> select name,population from city where District='heilongjiang';

#4.查询中国人口数量小于100000的城市
mysql> select name,population from city where population < 100000 and countrycode='CHN';

#5.模糊查询
#国家代码以H结尾的
mysql> select * from city where countrycode like '%H';
#国家代码以H开头的
mysql> select * from city where countrycode like 'H%';
#国家代码包含H的
mysql> select * from city where countrycode like '%H%';

#6.查询中国或美国的城市人口 or in
mysql> select * from city where countrycode='CHN' or countrycode='USA';
mysql> select * from city where countrycode in ('CHN','USA');
#排除
mysql> select * from city where countrycode != 'CHN' and countrycode != 'USA';
mysql> select * from city where countrycode not in ('CHN','USA');

#7.联合查询 union all
mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

4. select设置别名

#1.为每个查询字段设置别名显示
mysql> select name as '名称',population as '人口' from city limit 60;

六、select高级用法（连表查询,多表联查）传统连接

1.连表4. select设置别名查询

#集合
[qiudao,zhangyu,zhangyin]
[80,90,100]
#只写两个集合，并不知道里面的值是什么，也不知道关联，所以一般会给他们一个定义

id:[1,2,3]
name:[qiudao,zhangyu,zhangyin]

id:[1,2,3]
length:[80,90,100]
mark:[80,90,100]

#1.创建一个student表
mysql> create table student(id int,name varchar(10));

#2.创建一个成绩表
mysql> create table score(id int,mark int)

#3.查看表结构
mysql> desc student;
mysql> desc score;

#4.插入数据
mysql> insert into student values(1,'qiudao'),(2,'zhangyu'),(3,'yinyin');
mysql> insert into score values(1,80),(2,90),(3,100);

#5.查看数据
mysql> select * from student;
mysql> select * from score;

#6.连表查询数据
	#错误写法
	mysql> select student.name,score.mark from student,score;
	mysql> select student.name,score.mark from student,score where name='qiudao';
	#必须找出两个表相关联的值
	mysql> select student.name,score.mark from student,score where id=1;
	mysql> select student.name,score.mark from student,score where student.id=score.id;
	mysql> select student.name,score.mark from student,score where student.id=2;
#正确写法
mysql> select student.name,score.mark from student,score where student.id=score.id and name='qiudao';

2.连表查询练习

1）世界上小于100人的人口城市是哪个国家的？

#1.看看查询的内容有哪些
人口数量  城市名  国家名

#2.查看表字段
mysql> desc city;
mysql> desc country;
mysql> desc countrylanguage;

#3.找出要查询的值
人口数量  			城市名  		国家名
city.Population		city.name	  country.name

#4.找三个表相关联的字段
city.CountryCode
country.code
countrylanguage.CountryCode

#5.编写语句
mysql> select city.population,city.name,country.name from city,country where city.countrycode=country.code and city.population < 100;

2）查询人口数量小于100的城市在哪个国家,说的什么语言?

#1.看看查询的内容有哪些
人口数量  城市名  国家名  语言

#2.查看表字段
mysql> desc city;
mysql> desc country;
mysql> desc countrylanguage;

#3.找出要查询的值
人口数量  			城市名  		国家名  		语言
city.Population		city.name	  country.name	  countrylanguage.language

#4.找两个表相关联的字段
city.CountryCode
country.code

#5.编写语句
mysql> select city.population,city.name,country.name,countrylanguage.language from city,country,countrylanguage where city.CountryCode=country.code and country.code=countrylanguage.CountryCode and city.population < 100;

七、select高级用法 自连接

自连接的表要有共同的列名字和列数据

1.根据语句翻译要查询的内容

SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM city NATURAL JOIN countrylanguage 
WHERE population > 1000000
ORDER BY population;
#查询人口数量大于一百万的城市的国家代码和国家语言，并升序排序?

#刚才的题没有办法使用该连接方式，查不出来，因为没有相同字段，name字段相同，但是值不同
select city.population,city.name,country.name from city natural join country where city.population < 100;

注意:
	1.自己去查找两个表之间的关联字段(natural join) 主键:聚集索引
	2.查询的表内必须有相同的字段名和数据

八、select高级用法 内连接

#1.内连接格式:
select * from 表1 join 表2 on 关联条件 where 条件

#驱动，命中率概念
重点:表1 (小表)
	表2 (大表)

#1.查询人口数小于100的城市是哪个国家的，国家代码是多少
select city.name,city.countrycode,country.name 
from city join country on city.countrycode=country.code 
where city.population < 100;

#1.查询人口数小于100的城市是哪个国家的，人数是多少
select city.name,city.population,country.name
from country join city on city.countrycode=country.code
where city.population < 100;

九、select高级用法 外连接

1.左外连接

select city.name,city.countrycode,country.name 
from city left join country 
on city.countrycode=country.code 
and city.population < 100;

2.右外连接

select city.name,city.countrycode,country.name 
from city right join country 
on city.countrycode=country.code 
and city.population < 100;

十、字符集

1.什么是字符集

字符集：是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。
在计算机最早出来的时候,用的什么字符集?   ASCII
但是中国字很多博大精深，没办法用这个，所以有了自己的字符集 GBK GB2312
日本: shift_JIS
韩国: Euc-kr
各个国家不认识对方的编码不行，所以又推出了一个编码叫：unicode:万国编码 

字符集:  gbk: 汉字占2个字节
		utf8: 汉字占3个字节
		utf8mb4: 汉字占4个字节
		
#查看字符集
mysql> show charset;

2.校验规则

校验规则: utf8_general_ci
	1）ci：大小写不敏感
	2）cs或bin：大小写敏感

#一个表里面不可能出现同名不同大小写的字段
mysql> alter table city add name varchar(10);
ERROR 1060 (42S21): Duplicate column name 'name'

#查看校验规则
mysql> show collation；

3.统一字符集

我们操作从xshell ---> linux ---> mysql数据库
mysql数据库默认字符集，库的字符集，表的字符集都可以单独设置，所以要统一

#1.xshell可以演示修改字符集输入看看
临时修改报错命令为中文 : LANG=zh_CN.UTF-8
临时修改报错命令为中文 : LANG=us_EN.UTF-8

#2.修改系统字符集
Centos6 永久修改：[root@db03 ~]# vim /etc/sysconfig/i18n
Centos7 永久修改：[root@db03 ~]# vim /etc/locale.conf

#3.修改MySQL默认字符集:
1.cmake时候指定
cmake .
-DDEFAULT_CHARSET=UTF8 \
-DDEFAULT_COLLATION=UTF8_GENERAL_CI

2.配置文件指定
[mysqld]
character-set-server=utf8
#最好加上反向解析，安装目录和数据目录
skip-name-resolve
basedir=
datadir=

#4.如果不配置否则,建库
mysql> create database oldboy charset utf8 collate = utf8_general_ci;
mysql> create database oldboy charset utf8 default collate = utf8_general_ci;

#5.如果不配置否则,建库
mysql> CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT comment 'id',
`name` char(20) NOT NULL comment '名字',
PRIMARY KEY (`id`) comment '主键'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#6.修改数据库的字符集:
mysql> alter database lhd charset utf8;
Query OK, 1 row affected (0.00 sec)
	
mysql> show create database lhd;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| oldtian2 | CREATE DATABASE `oldtian2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+

#7.修改表的字符集:
mysql> alter table tb1 charset utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
	
mysql> show create table tb1;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| tb1   | CREATE TABLE `tb1` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

#8.转换字符集的前提条件
字符集范围必须属于包含关系
gbk  1-5000
utf8  0-9000
gb2312  1000-10000
EUC-kr  10-8000
 
EUC-kr支持韩语字符，日本字符，中国字符
gbk包含韩语字符，日本字符，中国字符，阿拉伯字符
那么EUC-kr可以转换成gbk字符

#9.批量修改字符集，30个库，300个表
1.我们先创建10个库
[root@db04 ~]# for n in `seq 10`;do mysql -uroot -p123456 -e "create database user$n charset gbk";done
2.库里创建表
[root@db04 ~]# for n in `seq 10`;do mysql -uroot -p123456 -e "use user$n;create table user$n(id int) charset gbk";done

#怎么批量修改数据库的字符集？？？？
单独改库不行，表是不会修改的
mysql> alter database user1 charset utf8;
mysql> show create database user1;
查看表
mysql> show create table user11;

3.先导出要改的数据库
[root@db04 ~]# mysqldump -uroot -p123456 -A > /tmp/123.sql
-B  到处指定库结构
[root@db04 ~]# vim /tmp/123.sql 
:%s#gbk#utf8#g

4.导入数据库
[root@db04 ~]# mysql -uroot -p123456 < /tmp/123.sql

6.写脚本修改

十一、 mysql权限管理

MySQL 赋予用户权限命令的简单格式可概括为：

grant 权限 on 数据库对象 to 用户

1、grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select on testdb.* to common_user@'%'
grant insert on testdb.* to common_user@'%'
grant update on testdb.* to common_user@'%'
grant delete on testdb.* to common_user@'%'

或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* to common_user@'%'

2、grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。

grant 创建、修改、删除 MySQL 数据表结构权限

grant create on testdb.* to developer@'192.168.0.%';
grant alter  on testdb.* to developer@'192.168.0.%';
grant drop   on testdb.* to developer@'192.168.0.%';

# grant 操作 MySQL 外键权限。

grant references on testdb.* to developer@'192.168.0.%';

# grant 操作 MySQL 临时表权限。

grant create temporary tables on testdb.* to developer@'192.168.0.%';

# grant 操作 MySQL 索引权限。

grant index on testdb.* to developer@'192.168.0.%';

# grant 操作 MySQL 视图、查看视图源代码 权限。

grant create view on testdb.* to developer@'192.168.0.%';
grant show   view on testdb.* to developer@'192.168.0.%';

# grant 操作 MySQL 存储过程、函数 权限。

grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status
grant alter  routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure
grant execute        on testdb.* to developer@'192.168.0.%';

3、grant 普通 DBA 管理某个 MySQL 数据库的权限。

grant all privileges on testdb.* to dba@'localhost'

# 其中，关键字 “privileges” 可以省略。

4、grant 高级 DBA 管理 MySQL 中所有数据库的权限。

grant all on *.* to dba@'localhost'

5、MySQL grant 权限，分别可以作用在多个层次上。

# (1) grant 作用在整个 MySQL 服务器上：

grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
grant all    on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库

# (2)grant 作用在单个数据库上：

grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。

# (3). grant 作用在单个数据表上：

grant select, insert, update, delete on testdb.orders to dba@localhost;

这里在给一个用户授权多张表时，可以多次执行以上语句。例如：

grant select(user_id,username) on test.users to mo_user@'%' identified by '123345';
grant select on test.table_name to mo_user@'%' identified by '123345';

# (4)grant 作用在表中的列上：

grant select(id, se, rank) on testdb.table_name to dba@localhost;

# (5) grant 作用在存储过程、函数上：

grant execute on procedure testdb.pr_add to 'dba'@'localhost'
grant execute on function testdb.fn_add to 'dba'@'localhost'

6、查看 MySQL 用户权限

# 查看当前用户（自己）权限：

show grants;

# 查看其他 MySQL 用户权限：

show grants for dba@localhost;

7、撤销已经赋予给 MySQL 用户权限的权限。

# revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

grant  all on *.* to   dba@localhost;
revoke all on *.* from dba@localhost;

8、MySQL grant、revoke 用户权限注意事项

 # 1) grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

# 2)如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“

grant select on testdb.* to dba@localhost with grant option;