By CaesarChang
~关注我 带你看更多精品技术和面试必备
SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）
SQL语句中的快捷键
\G 格式化输出（文本式，竖立显示）
\s 查看服务器端信息
\c 结束命令输入操作
\q 退出当前sql命令行模式
\h 查看帮助
通过命令行来操作数据库
sql语句特点：
1 不区分大小写
2 已分号结尾
3 \C 退出
数据库操作：
查看数据库 show databases;
创建数据库 create database 库名 default charset=utf8;
删除数据库 drop database 库名;
打开数据库 use 库名;
展示表名： show tables ; (前提进入了某一个库）
简介：
mysql -u root -p 你的密码；
登陆mysql
show databases; //查看所有数据库
use 库名；
选择需要操作的库，打开库
show tables；查看当前库所有表
select * from 表名；
select 字段 1, 字段 2, 字段 3 from 表名 ;
select * from 表名 where 字段 = 某个值 ;
//查看数据库某表的数据
select host,user from user;
// 查看 user表中的所有数据的 host和user字段列
create database 库名 default charset=utf8;
//创建数据库
create table 表名( 字段名 类型 字段约束, 字段名 类型 字段约束, 字段名 类型 字段约束, )engine=innodb default
charset=utf8;
//创建表
logid int(4) primary key not null auto_increment,
create table user(
name varchar(20),
age int,
sex char(1)
)engine=innodb default charset=utf8;
insert into user(name,age,sex) values('admin',26,'男');
insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);
//添加数据
desc 表名 ；
//查看 表结构
show create table users;
//查看建表语句
update 表名 set 字段=某个值 where 条件;
update 表名 set 字段1=值1,字段2=值2 where 条件;
//修改
delete from 表名 where 字段=某个值;
//删除表
exit / quit
//退出mysql
mysql里面的数据类型
定长串： char
1. 接受长度固定的字符串，其长度是在创建表时指定的。
char(7) 不管实际插入多少字符，它都会占用 7 个字符位置
2变长串 varchar
注意：
既然变长数据类型这样灵活，为什么还要使用定长数据类型？
回答： 因为性能， MySQL 处理定长列远比处理变长列快得多。
3Text 变长文本类型存储
decimal(5, 2) 表示数值总共5位, 小数占2位
tinyint 1字节(8位) 0-255。-128，127
int 4字节。 -21亿，21亿。0-42亿
float.
MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)
默认为有符号， 可以使用unsigned来是他无符号
二进制数据类型：
日期
select +
还有一堆哈 。先用先查就好（现） 比如month addtime。。。。。
表的字段约束
unsigned 无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以)
字段类型后面加括号限制宽度
char(5). varchar(7) 在字符类型后面加限制 表示 字符串的长度
int(4) 没有意义，默认无符号的 int 为 int(11) ，有符号的 int(10)
int(4) unsigned zerofifill 只有当给 int 类型设置有前导零时，设置 int 的宽度才有意义。
not null 不能为空 , 在操作数据库时如果输入该字段的数据为 NULL ，就会报错
default 设置默认值
primary key 主键不能为空 , 且唯一 . 一般和自动递增一起配合使用。
auto_increment 定义列为自增属性，一般用于主键，数值会自动加 1
unique 唯一索引 ( 数据不能重复 : 用户名 ) 可以增加查询速度 , 但是会降低插入和更新速度
运算符：
主键：
表中每一行都应该有可以唯一标识自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键值
MySQL 数据操作 DML
更改字段属性
alter table 表名 老字段名 新字段名 属性；
添加数据
格式： insert into 表名 [( 字段列表 )] values( 值列表 ...);
-- 标准添加（指定所有字段，给定所有的值）
mysql> insert into stu(id,name,age,sex,classid) values ( 1 , 'zhangsan' , 20 , 'm' , 'lamp138' );
Query OK, 1 row affected ( 0.13 sec)
-- 指定 部分 字段添加值
mysql> insert into stu(name,classid) value( 'lisi' , 'lamp138' );
Query OK, 1 row affected ( 0.11 sec)
-- 不指定 字段添加值
mysql> insert into stu value( null , 'wangwu' , 21 , 'w' , 'lamp138' );
Query OK, 1 row affected ( 0.22 sec)
-- 批量 添加值
mysql> insert into stu values
-> ( null , 'zhaoliu' , 25 , 'w' , 'lamp94' ),
-> ( null , 'uu01' , 26 , 'm' , 'lamp94' ),
-> ( null , 'uu02' , 28 , 'w' , 'lamp92' ),
-> ( null , 'qq02' , 24 , 'm' , 'lamp92' ),
-> ( null , 'uu03' , 32 , 'm' , 'lamp138' ),
-> ( null , 'qq03' , 23 , 'w' , 'lamp94' ),
-> ( null , 'aa' , 19 , 'm' , 'lamp138' );
Query OK, 7 rows affected ( 0.27 sec)
Records: 7 Duplicates: 0 Warnings: 0
修改数据
格式： update 表名 set 字段 1= 值 1, 字段 2= 值 2, 字段 n= 值 n... where 条件
-- 将 id 为 11 的 age 改为 35 ， sex 改为 m 值
mysql> update stu set age= 35 ,sex= 'm' where id= 11 ;
Query OK, 1 row affected ( 0.16 sec)
新增字段：
mysql> ALTER TABLE student ADD age INT(4); //向表中添加字段 age
末尾加上 first 则可以实现在表头添加字段。
-- 将 id 值为 12 和 14 的数据值 sex 改为 m ， classid 改为 lamp92
mysql> update stu set sex= 'm' ,classid= 'lamp92' where id= 12 or id= 14
-- 等价于下面
mysql> update stu set sex= 'm' ,classid= 'lamp92' where id in ( 12 , 14 ); //注意in的用法 比较简洁常用 in（X，X）；
Query OK, 2 rows affected ( 0.09 sec)
删除数据
格式： delete from 表名 [where 条件 ]
-- 删除stu表中id值为100的数据
mysql> delete from stu where id=100;
Query OK, 0 rows affected (0.00 sec)
-- 删除stu表中id值为20到30的数据
mysql> delete from stu where id>=20 and id<=30;
Query OK, 0 rows affected (0.00 sec)
-- （等级于上面写法）
mysql> delete from stu where id between 20 and 30;
Query OK, 0 rows affected (0.00 sec)
-- 删除stu表中id值大于200的数据
mysql> delete from stu where id>200;
Query OK, 0 rows affected (0.00 sec)
DQL-MySQL数据查询SQL
语法格式：
select 字段列表 / * from 表名
[ where 搜索条件 ]
[ group by 分组字段 [ having 分组条件 ]]
[ order by 排序字段 排序规则 ]
[ limit 分页参数 ]
基础查询
# 查询表中所有列 所有数据
select * from users;
# 指定字段列表进行查询
select id,name,phone from users;
Where 条件查询
可以使用 and 或者 or 指定一个或多个条件
示例：
-- 查询 users 表中 age > 22 的数据
select * from users where age > 22 ;
-- 查询 users 表中 name= 某个条件值 的数据
select * from users where name = ' 王五 ' ;
-- 查询 users 表中 年龄 在22到25之间 的数据
select * from users where age >= 22 and age <= 25 ;
select * from users where age between 22 and 25 ;
-- 查询 users 表中 年龄 不在22到25 之间的数据
select * from users where age < 22 or age > 25 ;
select * from users where age not between 22 and 25 ;
-- 查询 users 表中 年龄在 22 到 25 之间的女生信息
select * from users where age >= 22 and age <= 25 and sex = ' 女 ' ;
and 和 or 使用时注意 假设要求 查询 users 表中 年龄为 22 或者 25 的女生信息
select * from users where age=22 or age = 25 and sex = ' 女 ';
思考上面的语句能否返回符合条件的数据？
实际查询结果并不符合要求？
Like 子句
我们可以在 where 条件中使用 =,<,> 等符合进行条件的过滤，但是当想查询某个字段是否包含时如何过滤？
可以使用 like 语句进行某个字段的模糊搜索，
例如： 查询 name 字段中包含五的数据
select * from users where age= 22 or age = 25 and sex = ' 女 ' ;
+------+--------+------+-------+-------+------+------+
| id | name | age | phone | email | sex | mm |
+------+--------+------+-------+-------+------+------+
| 1 | 章三 | 22 | | NULL | 男 | 0 |
| 1002 | cc | 25 | 123 | NULL | 女 | NULL |
+------+--------+------+-------+-------+------+------+
2 rows in set ( 0.00 sec)
-- 上面的查询结果并不符合 查询条件的要求。
-- 问题出在 sql 计算的顺序上，sql会优先处理and条件，所以上面的sql语句就变成了
select * from users where( age= 22) or( age = 25 and sex = ' 女 ') ;
-- 查询变成了为年龄 22 的不管性别，或者年龄为 25 的女生
-- 如何改造 sql 符合我们的查询条件呢？
-- 使用小括号来关联相同的条件
select * from users where (age= 22 or age = 25 ) and sex = ' 女 ' ;
+------+------+------+-------+-------+------+------+
| id | name | age | phone | email | sex | mm |
+------+------+------+-------+-------+------+------+
| 1002 | cc | 25 | 123 | NULL | 女 | NULL |
+------+------+------+-------+-------+------+------+
1 row in set ( 0.00 sec)
-- like 语句 like某个确定的值 和。where name = '王五' 是一样
select * from users where name like ' 王五 ' ;
-- 使用 % 模糊搜索。 % 代表任意个任意字符
-- 查询 name 字段中包含五的
select * from users where name like ' %五%' ;
注意： where 子句中的 like 在使用 % 或者 _ 进行模糊搜索时， 效率不高， 使用时注意：
尽可能的不去使用 % 或者 _
如果需要使用，也尽可能不要把通配符放在开头处
Mysql 中的统计函数（聚合函数）
max(),min(),count(),sum(),avg()
-- 查询 name 字段中最后一个字符 为 五的
select * from users where name like '% 五 ' ;
-- 查询 name 字段中第一个字符 为 王 的
select * from users where name like ' 王 %' ;
-- 使用 _ 单个的下划线。表示一个任意字符，使用和 % 类似
-- 查询表中 name 字段为两个字符的数据
select * from users where name like '__' ;
-- 查询 name 字段最后为五，的两个字符的数据
select * from users where name like '_ 五 ' ;
# 计算 users 表中 最大年龄，最小年龄，年龄和及平均年龄
select max (age), min (age), sum (age), avg (age) from users;
+----------+----------+----------+----------+
| max (age) | min (age) | sum (age) | avg (age) |
+----------+----------+----------+----------+
| 28 | 20 | 202 | 22.4444 |
+----------+----------+----------+----------+
-- 上面数据中的列都是在查询时使用的函数名，不方便阅读和后期的调用，可以通过别名方式 美化
select max (age) as max_age,
min (age) min_age, sum (age) as sum_age,
avg (age) as avg_age
from users; //使用 as 来起别名
+---------+---------+---------+---------+
| max_age | min_age | sum_age | avg_age |
+---------+---------+---------+---------+
| 28 | 20 | 202 | 22.4444 |
+---------+---------+---------+---------+
-- 统计 users 表中的数据量
select count (*) from users;
+----------+
| count (*) |
+----------+
| 9 |
+----------+
select count (id) from users;
+-----------+
| count (id) |
+-----------+ 聚合函数除了以上简单的使用意外，通常情况下都是配合着分组进行数据的统计和计算
Group BY 分组
group by 语句根据一个或多个列对结果集进行分组
一般情况下，是用与数据的统计或计算，配合聚合函数使用
| 9 |
+-----------+
-- 上面的两个统计，分别使用了 count(*) 和 count(id), 结果目前都一样，有什么区别？
-- count(*) 是按照 users 表中所有的列进行数据的统计，只要其中一列上有数据，就可以计算
-- count(id) 是按照指定的 id 字段进行统计，也可以使用别的字段进行统计，
-- 但是注意，如果指定的列上出现了 NULL 值，那么为 NULL 的这个数据不会被统计
-- 假设有下面这样的一张表需要统计
+------+-----------+------+--------+-----------+------+------+
| id | name | age | phone | email | sex | mm |
+------+-----------+------+--------+-----------+------+------+
| 1 | 章三 | 22 | | NULL | 男 | 0 |
| 2 | 李四 | 20 | | NULL | 女 | 0 |
| 5 | 王五 | 24 | 10011 | ww @qq.com | 男 | 0 |
| 1000 | aa | 20 | 123 | NULL | 女 | NULL |
| 1001 | bb | 20 | 123456 | NULL | 女 | NULL |
| 1002 | cc | 25 | 123 | NULL | 女 | NULL |
| 1003 | dd | 20 | 456 | NULL | 女 | NULL |
| 1004 | ff | 28 | 789 | NULL | 男 | NULL |
| 1005 | 王五六 | 23 | 890 | NULL | NULL | NULL |
+------+-----------+------+--------+-----------+------+------+
9 rows in set ( 0.00 sec)
-- 如果按照 sex 这一列进行统计，结果就是 8 个而不是 9 个，因为 sex 这一列中有 NULL 值存在
mysql> select count (sex) from users;
+------------+
| count (sex) |
+------------+
| 8 |
+------------+
-- 统计 users 表中 男女生人数 ,
-- 很明显按照上面的需要，可以写出两个语句进行分别统计
select count (*) from users where sex = ' 女 ' ;
select count (*) from users where sex = ' 男 ' ;
-- 可以使用分组进行统计，更方便
select sex, count (*) from users group by sex;
+------+----------+
| sex | count (*) |
+------+----------+
| 男 | 4 |
| 女 | 5 |
+------+----------+Having 子句
having 时在分组聚合计算后，对结果再一次进行过滤，类似于 where ，
where过滤的是行数据 ， having过滤的是分组数据
Order by 排序
我们在 mysql 中使用 select 的语句查询的数据结果是根据数据在底层文件的结构来排序的，
首先不要依赖默认的排序，另外在需要排序时要使用 orderby对返回的结果进行排序
desc降序
-- 统计 1 班和 2 班的人数
select classid, count (*) from users group by classid;
+---------+----------+
| classid | count (*) |
+---------+----------+
| 1 | 5 |
| 2 | 4 |
+---------+----------+
-- 分别统计每个班级的男女生人数
select classid,sex, count (*) as num from users group by classid,sex;
+---------+------+-----+
| classid | sex | num |
+---------+------+-----+
| 1 | 男 | 2 |
| 1 | 女 | 3 |
| 2 | 男 | 2 |
| 2 | 女 | 2 |
+---------+------+-----+
# 注意 ，在使用。 g roup by分组时，一般除了聚合函数，其它在 select后面出现的字段列 都需要 出现在grouop by 后
面
-- 要统计班级人数
select classid, count (*) from users group by classid;
-- 统计班级人数，并且要人数达到 5 人及以上
select classid, count (*) as num from users group by classid having num >= 5 ; Limit 数据分页
limit n 提取n条数据，
limit m,n 跳过m跳数据，提取n条数据
课后练习题
-- 查询 users 表中的数据，只要 3 条
select * from users limit 3 ;
-- 跳过前 4 条数据，再取 3 条数据
select * from users limit 4 , 3 ;
-- limit 一般应用在数据分页上面
-- 例如每页显示 10 条数据，第三页的 limit 应该怎么写？ 思考
第一页 limit 0 , 10
第二页 limit 10 , 10
第三页 limit 20 , 10
第四页 limit 30 , 10
Mysql数据库授权与导入导出
权限管理
mysql 中的 root 用户是数据库中权限最高的用户，千万不要用在项目中。
可以给不同的用户，或者项目，创建不同的 mysql 用户，并适当的授权，完成数据库的相关操作
这样就一定程度上保证了数据库的安全。
创建用户的语法格式：
grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’;
grant select , insert on tlxy.* to zhangsan @'%' identified by '123456' ;
# 用户 lisi。密码 123456 可以对tlxy库中的所有表有 所有操作权限
grant all on tlxy.* to lisi @'%' identified by '123456' ;
# 删除用户
drop user 'lisi' @'%' ;
导出：
# 不要进入 mysql ，然后输入以下命令 导出某个库中的数据
mysqldump -u root -p tlxy > ~/Desktop/code/tlxy.sql
# 不要进入 mysql ，然后输入以下命令 导出某个库中 指定的表的数据
mysqldump -u root -p tlxy tts > ~/Desktop/code/tlxy-tts.sql
导入：
# 在新的数据库中 导入 备份的数据，导入导出的 sql 文件
mysql -u root -p ops < ./tlxy.sql
# 把导出的表 sql 导入数据库
mysql -u root -p ops < ./tlxy-tts.sql # 在 mysql 中 创建一个 zhangsan 用户，授权可以对 tlxy 这个库中的所有表 进行 添加和查询 的权限
-- 修改表名
rename table old_table to new_table; -- 或者 alter table old_table rename as new_table; --
修改列名称
alter table table_name change column old_name new_name varchar(255); --
修改字段类型
alter table table_name modify column column_name varchar(255) default '' COMMENT '注释';
