1. 数据库介绍1.1 什么是数据库

数据的仓库，职责是：管理数据和存取数据

1.2 什么是数据

世界万事万物都可以描述成数据，比如一个人、一辆车、一套房，都可以用一条或多条数据来描述

1.3 数据库种类

按模型分类，最常见是关系型数据库和非关系型数据库

​

2. SQL语句2.1 SQL分类

数据定义语言DDL，数据操纵语言DML，数据查询语言DQL，数据控制语言DCL

​​

2.2 数据库2.2.1 展示数据库

show databases;

2.2.2 创建数据库

# utf-8CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

2.2.3 使用数据库

use 数据库名称

2.3 数据表结构2.3.1 展示数据表

show tables;

2.3.2 创建表

create table stu(    id int(10) unsigned not null auto_increment primary key comment '主键',    name varchar(255) comment '姓名',    sex tinyint(1) not null default 0 comment '性别:0表示男，1表示女',    age int(3) not null comment '年龄',    grade char(5) not null comment '班级')engine=MyIsam default charset=utf8;

2.3.3 查看表的定义

mysql> desc stu;+-------+------------------+------+-----+---------+----------------+| Field | Type             | Null | Key | Default | Extra          |+-------+------------------+------+-----+---------+----------------+| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment || name  | varchar(255)     | YES  |     | NULL    |                || sex   | tinyint(1)       | NO   |     | 0       |                || age   | int(3)           | NO   |     | NULL    |                || grade | char(5)          | NO   |     | NULL    |                |+-------+------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)

2.3.4 获得更全面表定义信心

mysql> show create table stu \G;*************************** 1. row ***************************       Table: stuCreate Table: CREATE TABLE `stu` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',  `name` varchar(255) DEFAULT NULL COMMENT '姓名',  `sex` tinyint(1) NOT NULL DEFAULT '0' COMMENT '性别:0表示男，1表示女',  `age` int(3) NOT NULL COMMENT '年龄',  `grade` char(5) NOT NULL COMMENT '班级',  PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.02 sec)

2.3.4 修改表

①修改表stu的name字段定义，将varchar(255)改成varchar(30):mysql> alter table stu modify name varchar(30);-- Query OK, 0 rows affected (0.04 sec)-- Records: 0  Duplicates: 0  Warnings: 0②增加表字段的操作，在表stu中添加字段adress，类型为varchar(128):mysql> alter table stu add column adress varchar(128);-- Query OK, 0 rows affected (0.01 sec)-- Records: 0  Duplicates: 0  Warnings: 0③删除表字段的操作，在表stu中删除字段adress,age,grade:mysql> alter table stu drop column adress;-- Query OK, 0 rows affected (0.01 sec)-- Records: 0  Duplicates: 0  Warnings: 0mysql> alter table stu drop column age;-- Query OK, 0 rows affected (0.01 sec)-- Records: 0  Duplicates: 0  Warnings: 0mysql> alter table stu drop column grade;-- Query OK, 0 rows affected (0.01 sec)-- Records: 0  Duplicates: 0  Warnings: 0④字段改名，将sex修改成sex1，同时修改字段类型为tinyint(2):mysql> alter table stu change column sex sex1 tinyint(2);-- Query OK, 0 rows affected (0.03 sec)-- Records: 0  Duplicates: 0  Warnings: 0⑤字段增加或者修改语法(add/change/modify)中，都有一个可选项first|after column_name来修改字段的位置mysql> alter table stu add column age int(3) after name;-- Query OK, 0 rows affected (0.01 sec)-- Records: 0  Duplicates: 0  Warnings: 0⑥更改表名mysql> alter table stu rename student;-- Query OK, 0 rows affected (0.00 sec)

2.3.5 清空表

delete from 表名;truncate table 表名;

2.3.6 删除表

drop table 表名;

2.4 数据表操作2.4.1 插入记录

mysql> insert into student(name,sex,age)values('张三',0,25);-- Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+------+------+------+| id | name | sex  | age  |+----+------+------+------+|  1 | 张三 |    0 |   25 |+----+------+------+------+1 row in set (0.00 sec)-- 可以插入多条数据，数据之间用逗号隔开mysql> insert into student(name,sex,age)values('李四',0,26),('王二',1,20);-- Query OK, 2 rows affected (0.00 sec)-- Records: 2  Duplicates: 0  Warnings: 0mysql> select * from student;+----+------+------+------+| id | name | sex  | age  |+----+------+------+------+|  1 | 张三 |    0 |   25 ||  2 | 李四 |    0 |   26 ||  3 | 王二 |    1 |   20 |+----+------+------+------+3 rows in set (0.00 sec)

2.4.2 更新记录

mysql> update student set age=18 where name='王二';-- Query OK, 1 row affected (0.00 sec)-- Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from student;+----+------+------+------+| id | name | sex  | age  |+----+------+------+------+|  1 | 张三 |    0 |   25 ||  2 | 李四 |    0 |   26 ||  3 | 王二 |    1 |   18 |+----+------+------+------+3 rows in set (0.00 sec)多表关联的修改内容例子：update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;

2.4.3 删除记录

mysql> delete from student where id=5;-- Query OK, 1 row affected (0.00 sec)如果from后面的表名用别名，用delete后面也要用相应的别名，否则会提示语法错误delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;同时删除两表中deptno为3的记录注意：不管单表还是多表，不加where条件将会把表的所有记录删除，所以操作时一定要小心

2.4.4 查询记录2.4.4.1 将所有记录都选出来

mysql> select * from student;+----+------+------+------+| id | name | sex  | age  |+----+------+------+------+|  1 | 张三 |    0 |   25 ||  2 | 李四 |    0 |   26 ||  3 | 王二 |    1 |   18 ||  4 | 小磊 |    1 |   18 |+----+------+------+------+4 rows in set (0.00 sec)

2.4.4.2 只查所需要字段

mysql> select name,age from student;+------+------+| name | age  |+------+------+| 张三 |   25 || 李四 |   26 || 王二 |   18 || 小磊 |   18 |+------+------+4 rows in set (0.00 sec)

2.4.4.3 记录去掉重复后显示出来

mysql> select distinct age from student;+------+| age  |+------+|   25 ||   26 ||   18 |+------+3 rows in set (0.00 sec)

2.4.4.4 按照条件来查询

mysql> select * from student where age=18;+----+------+------+------+| id | name | sex  | age  |+----+------+------+------+|  3 | 王二 |    1 |   18 ||  4 | 小磊 |    1 |   18 |+----+------+------+------+2 rows in set (0.00 sec)

2.4.4.5 按照某字段来排序，ASC升序(默认)，DESC降序

mysql> select * from student order by age;+----+------+------+------+| id | name | sex  | age  |+----+------+------+------+|  3 | 王二 |    1 |   18 ||  4 | 小磊 |    1 |   18 ||  1 | 张三 |    0 |   25 ||  2 | 李四 |    0 |   26 |+----+------+------+------+4 rows in set (0.00 sec)

2.4.4.6 按照某条件后只显示n条来显示

mysql> select * from student limit 2;+----+------+------+------+| id | name | sex  | age  |+----+------+------+------+|  1 | 张三 |    0 |   25 ||  2 | 李四 |    0 |   26 |+----+------+------+------+2 rows in set (0.00 sec)从某条记录开始显示也可以，前面代表开始序号，后面代表显示的条数mysql> select * from student limit 2,2;+----+------+------+------+| id | name | sex  | age  |+----+------+------+------+|  3 | 王二 |    1 |   18 ||  4 | 小磊 |    1 |   18 |+----+------+------+------+2 rows in set (0.00 sec)

2.4.4.7 聚合操作

select [field1,field2,....fieldn] fun_name from tablename [where where_contition][group by field1,field,....fieldn [with rollup]][having where_contition];fun_name 表示要做的聚合操作，也就是聚合函数，常用的有sum(),count(),max(),min(),avg(),std(),group_concat(), group by 关键字 表示要进行分类聚合的字段with rollup 是可选语法，表明是否对分类聚合后的结果进行再汇总having 关键字 表示对分类后的结果再进行条件的过滤

mysql> select count(1) from student;+----------+| count(1) |+----------+|        4 |+----------+1 row in set (0.00 sec)mysql> select age,count(1) from student group by age;+------+----------+| age  | count(1) |+------+----------+|   18 |        2 ||   25 |        1 ||   26 |        1 |+------+----------+3 rows in set (0.00 sec)按条件统计后汇总mysql> select age,count(1) from student group by age with rollup;+------+----------+| age  | count(1) |+------+----------+|   18 |        2 ||   25 |        1 ||   26 |        1 || NULL |        4 |+------+----------+4 rows in set (0.05 sec)mysql> select sum(age),max(age),min(age) from student;+----------+----------+----------+| sum(age) | max(age) | min(age) |+----------+----------+----------+|       87 |       26 |       18 |+----------+----------+----------+1 row in set (0.04 sec)⑧表连接内连接：选出两张表中互相匹配的记录select ename,deptname from emp,dept where emp.deptno=dept.deptno;外连接：会选出其他不匹配的记录，分为左连接和右连接左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录select ename,deptname from emp left join dept on emp.deptno=dept.deptno;右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录select ename,deptname from dept right join emp on dept.deptno=emp,deptno;⑨子查询在某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候就需要用到子查询。用于子查询的关键字主要包括in,not in,=,!=,exists,not exists等select * from emp where deptno in(select deptno from dept);⑩将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来，这个时候需要用union和union all 关键字来实现这样的功能select * from t1 union\union all select * from t2 union all 是把结果集直接合并在一起，而union是将union all 后的结果进行一次distinct去除重复记录后的结果union all用法实例：mysql>select deptno from emp->union all->select deptno from dept;union 用法实例mysql>select deptno from emp->union->select deptno from dept;

3. MySQL数据类型3.1 整型

MySQL数据类型

含义（有符号）

tinyint(m)

1个字节 范围（-128~127）

smallint(m)

2个字节 范围（-32768~32767）

mediumint(m)

3个字节 范围（-8388608~8388607）

int(m)

4个字节 范围（-2147483648~2147483647）

bigint(m)

8个字节 范围（+-9.22*10的18次方）

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)

3.2 浮点数（float和double）

MySQL数据类型

含义

float(m,d)

单精度浮点型 8位精度（4字节） m总个数，d小数位

double(m,d)

双精度浮点型 16位精度（8字节） m总个数，d小数位

设一个字段定义位float(5,3),如果输入一个数123.45678，实际数据库里存的是123.457，但总个数还以实际为准，即6位

3.3 定点数

浮点型在数据库中存放的是近似值，而定点型在数据库中存放的是精确值 decimal(m,d) 参数m<65是总个数，d<30且d<m是小数位

3.4 字符串

MySQL数据类型

含义

char(n)

固定长度， 最多255个字符

varchar(n)

固定长度，最多65535个字符

tinytext

可变长度，最多255个字符

text

可变长度，最多65535个字符

mediumtext

可变长度，最多2的24次方-1个字符

longtext

可变长度，最多2的32次方-1个字符

char和varchar：char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。char类型的字符串检索速度要比varchar类型的快。varchar和text：varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。text类型不能有默认值。varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。3.5 日期时间类型

MySQL数据类型

含义

date

日期'2018-12-2'

time

时间'12:25:36'

datetime

日期时间'2018-12-2 22:06-50'

timestamp

自动存储记录修改时间

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。3.6 其他数据类型的属性

MySQL数据类型

含义

NUll

数据列可允许NULL值

NOT NULL

数据列不允许NULL值

DEFAULT

默认值

PRIMARY KEY

主键

AUTO_INCREMENT

自动递增，适用于整数类型

UNSIGNED

无符号

CHARACTER SET name

指定一个字符集

4. 用户管理4.1 查看系统用户表

use mysql;SELECT `Host`,`User`,`Password` FROM `user`;

4.2 创建用户

create user '用户名'@'IP地址' identified by '密码';create user 'test'@'%' identified by '123456';

4.3 修改用户

rename user '用户名'@'IP地址' to '新用户名'@'新IP地址';rename user 'test'@'%' to 'test1'@'localhost';

4.4 修改密码

set password for '用户名'@'IP地址' = Password('新密码');set password for 'test1'@'localhost' = Password('666666');

4.5 删除用户

drop user '用户名'@'IP地址';drop user 'test'@'%';

5. 权限设置5.1 查看权限

show grants for '用户名'@'IP地址';

MariaDB [mysql]> show grants for 'root'@'%';+--------------------------------------------------------------------------------------------------------------------------------+| Grants for root@%                                                                                                              |+--------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*31ADC152D587D32F35070C17BDD7A31A74024365' WITH GRANT OPTION |+--------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

5.2 授权

grant 权限 on 数据库.表 to '用户名'@'IP地址';

5.2.3 权限

all privileges  除grant外的所有权限select          仅查权限select,insert   查和插入权限...usage                   无访问权限alter                   使用alter tablealter routine           使用alter procedure和drop procedurecreate                  使用create tablecreate routine          使用create procedurecreate temporary tables 使用create temporary tablescreate user             使用create user、drop user、rename user和revoke  all privilegescreate view             使用create viewdelete                  使用deletedrop                    使用drop tableexecute                 使用call和存储过程file                    使用select into outfile 和 load data infilegrant option            使用grant 和 revokeindex                   使用indexinsert                  使用insertlock tables             使用lock tableprocess                 使用show full processlistselect                  使用selectshow databases          使用show databasesshow view               使用show viewupdate                  使用updatereload                  使用flushshutdown                使用mysqladmin shutdown(关闭MySQL)super                   使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆replication client      服务器位置的访问replication slave       由复制从属使用

5.2.4 数据库.表

数据库名.*           数据库中的所有数据库名.表          指定数据库中的某张表数据库名.存储过程     指定数据库中的存储过程*.*                 所有数据库

5.2.5 '用户名'@'IP地址'

用户名@IP地址         用户只能在改IP下才能访问用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)用户名@%             用户可以再任意IP下访问

赋予所有的权限

grant all privileges on *.* to '用户名'@'IP地址';flush privileges

5.3 取消权限

revoke 权限 on 数据库.表 from '用户'@'IP地址';# 取消所有的权限revoke all privileges,grant option from 'test'@'%';flush privileges;

6. 数据库的备份和还原6.1 数据库的备份

备份单个数据库数据和结构mysqldump -uroot -p db > db.sql备份单个数据库结构mysqldump -uroot -p db -d > db_struct.sql备份单个数据库数据mysqldump -uroot -p db -t > db_data.sql备份多个表的数据和结构mysqldump -uroot -p db t1 t2 > db.sql备份全部数据库的数据和结构mysqldump -uroot -p -A > all.sql备份全部数据库的结构mysqldump -uroot -p -A -d > all_struct.sql备份全部数据库的数据mysqldump -uroot -p -A -t > all_data.sql一次备份多个数据库mysqldump -uroot -p --database db1 db2 > db.sql

6.2 数据库的还原

还原全部数据库mysql>source all.sql;mysql -uroot -p > all.sql还原单个数据库mysql>use db;mysql>source all.sql;mysql -uroot -p db < all.sql还原多个数据库mysql>source all.sql;mysql -uroot -p < all.sql;