mysql常用语法

操作数据库 CRUD

1.C（create）创建

// 创建数据库，先判断是否存在，不存在就创建，并制定数据库的字符集
create database if not exists 数据库名称 character set 字符集名称（一般 utf8 ）;

2.R（retrieve）查询

// 查看所有数据库
show databases;

// 查看某个数据库字符集
show create database 数据库名称;

3.U（update）修改

// 修改某个数据库的字符集
alter database 数据库名称 character set 字符集名称（一般 utf8 ）;

4.D（delete）删除

// 删除数据库，先判断是否存在，存在就删除
drop database if exists 数据库名称;

5.普通操作

// 当前数据库
select database();

// 使用数据库
use 数据库名称;

操作表

1.C（create）创建

// 常用类型如下
1.int 整数类型
2.double(5,2) 小数类型：共5位数字，小数点后保留2位
3.date 日期：年月日形式，2020-02-02
4.datetime 日期：年月日时分秒形式，2020-02-02 12:00:00
5.timestamp 时间戳：年月日时分秒形式，2020-02-02 12:00:00
6.varchar(20) 字符串类型：最多20个字符

// 创建表
create table 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    ...
    列名n 数据类型n
);

// 复制表
create table 新表名 like 已存在的表名;

// 测试  
// 注意：double(5,2)共5位数字，小数点后保留2位
// 注意：insert_time timestamp 最后一项没有逗号
create table person(
  id int,
  name varchar(20),
  weight double(5,2),  
  birthday date,
  insert_time timestamp
);

2.R（retrieve）查询

// 查看所有表
show tables;

// 查看某个表结构
desc 表名;

// 查看某个表字符集
show create table表名;

3.U（update）修改

// 修改表名
alter table 表名 rename to 新表名;

// 修改表的字符集
alter table 表名 character set 字符集（utf8）;

// 添加一列
alter table 表名 add 列名 数据类型;

// 修改列名称
alter table 表名 change 列名 新列名 新数据类型;

// 修改列数据类型
alter table 表名 modify 列名 新数据类型;

// 删除列
alter table 表名 drop 列名;

4.D（delete）删除

// 删除表，先判断是否存在，存在就删除
drop table if exists 表名;

增删改数据（DML）

1.添加数据

// 中括号中列名可省略，省略后values中必须全部赋值。
// 赋值时，除了数字类型，其他类型都必须使用双引号
insert into 表名[(列1，列2，...列n)] values(数据1，数据2，...数据n);

2.删除数据

// 中括号中条件可省略，注意：省略后，会逐条删除全部数据。
delete from 表名 [where 条件]；

// 先删除表，然后创建一张相同的空表，删除全部数据的效率高。
truncate table 表名；

3.修改数据

// 中括号中条件可省略，注意：省略后，会逐条删除全部数据。
update 表名 set 列1 = 数据1，列2 = 数据2，列n = 数据n [where 列=值]；

查数据（DQL）

查询的基本语法字段
select 字段列表
from 表名列表
where 条件列表
group by 分组列表
having 分组后的条件
order by 排序
limit 分页

// 查看表中所有数据
select * from 表名；

// 去除重复数据
select distinct 字段名 from 表名；

// 计算数字数据，ifnull表示，如果是null值默认为后面的值。
select num01, num02, ifnull(num1,0) + ifnull(num2,1) from 表名；

// 起别名， as可以省略
select num01 别名, num02 别名, ifnull(num1,0) + ifnull(num2,1) [as] 别名 from 表名；

where查询条件示例，范围取值有： > < = >= <= !=

// 各种
select * from person where age = 20;
select * from person where age >= 20;
select * from person where age != 20;

// 20到30之间
select * from person where age >= 20 && age <= 30;
select * from person where age >= 20 and age <= 30;
select * from person where age between 20 and 30；

// 等于15,20,25岁的人
select * from person where age = 15 or age = 20 or age = 25;
select * from person where age in (15,20,25);

where查询null值，是null：is null 不是null：is not null

// 判断值是否为 null 必须使用 is null 或 is not null
select * from person where age is null；
select * from person where age is not null；

// 错误写法，错误写法，错误写法，null不是值，不能 = null
select * from person where age = null；

where查询的模糊查询：like，一个占位符_ 多个占位符%

// 姓马的人
select * from person where name like "马%"；

// 第三个字为马的人，两个"_"代表两个字，后面字符不限加"%"
select * from person where name like "__马%"；

// 姓名是3个字的人，只需要3个下划线 
select * from person where name like "___"；

// 包含马字的人
select * from person where name like "%马%"；

排序查询：ASC 升序（默认），DESC降序

// 按照数学成绩排名，只有数学成绩相等时，按照英语成绩排名。
select * from student order by math ASC , english DESC;

聚合函数：将一列数据作为整体，进行纵向计算。

// count：统计数据个数，一般选择使用主键统计个数
select count(id) from student;

// max：数据中最大值
select max(math) from student;

// min：数据中最小值
select min(math) from student;

// sum：数据和
select sum(math) from student;

// avg：数据平均数
select avg(math) from student;

分组：group by

// 按照性别分组，分别查询男、女的平均分
select sex , AVG(math) COUNT(id) from student group by sex;

// 按照性别分组，分别查询男、女的平均分
// 要求1：分数大于60分才参与计算
select sex , AVG(math) from student where math > 60 group by sex;

// 按照性别分组，分别查询男、女的平均分
// 要求1：分数大于60分才参与计算
// 要求2：分组后，组员数大于2，才参与计算
select sex , AVG(math) from student where math>60 group by sex having COUNT(id)>2;

// where 与 having 的区别
// 1.where在分组前限定，满足条件才能进组，限定的是数据。having在分组后限定，当组满足条件才能被查询，限定的是一个组。
// 2.where后不可以跟聚合函数，having后可以跟聚合函数。

分页查询：limit 起点数据的索引index，每页查询的条数
公式：起点数据的索引index =（当前页码-1）* 每页条数

// 第一页 
select * from student limit 0,5;
// 第二页
select * from student limit 5,5;
// 第三页
select * from student limit 10,5;

多表关系

一对多：一个旅游线路分类中有多个旅游线路。
1.旅游线路分类表（1）
2.旅游线路表（n）

多对多：多个用户可以收藏多个旅游线路。
1.旅游线路表
2.用户表

// cid 旅游线路分类主键，自动增长
// cname 旅游线路分类名称，非空，唯一
create table tab_category(
  cid int  primary key auto_increment,
  cname varchar(100) not null unique 
)

// rid 旅游线路主键，自动增长
// rname 旅游线路名称，非空，唯一
// cid 外键，关联 tab_category表的主键 cid。
create table tab_route(
  rid int primary key auto_increment,
  rname varchar(100) not null unique,
  price double,
  rdate date,
  cid int,
  foreign key (cid) references tab_category(cid)
)

// uid 用户主键，自动增长
// uname 用户名，非空，唯一
create table tab_user(
  uid int primary key auto_increment,
  uname varchar(20) not null unique,
  sex char(1) default '男'
)

// primary key(rid,uid) 联合主键
// rid 外键，关联 tab_route(rid)
// uid 外键，关联tab_user(uid)
create table tab_favorite(
  rid int,
  uid int,
  primary key(rid,uid),
  foreign key (rid) references tab_route(rid),
  foreign key (uid) references tab_user(uid)数据库
)

数据库的备份与还原

语法：

• 备份：mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
• 还原：登录数据库 -- 创建数据库 -- 使用数据库 -- 执行文件：source 文件路径

子查询：子查询可作为条件使用运算

子查询结果为单行单列：运算符（ > < = <= >= ）

// 查询  tab_01.num 小于 table_02.num 平均值的数据
select * from tab_01 where tab_01.num < (select AVG(num) from table_02);

子查询结果为多行单列：运算符（ in ）

// 先查询 tab_c 中研发部和技术部的数据，拿到n条数据后，使用 in() 查询 tab 表中的数据
select * from tab where tab.cid in(select id from tab_c where tab_c.name in("研发部","技术部"));

子查询结果为多行多列：把子查询结果当做虚拟表使用

// 子查询格式
select 
  * 
from 
  tab1 as t1,
  (select * from tab2 where tab2.age > 1) as t2  
where 
  t1.id = t2.cid;

// 可以转化为普通内连接
select 
  *
from
  tab1, tab2
where
  tab1.id = tab2.cid
  and tab2.age > 1;

事务

1.概念：如果一个包含多个步骤的操作被事务管理，那么这些操作要么同时成功，要么同时失败。
2.操作：开启事务：start transaction 回滚：rollback 提交：commit
3.四大特征：

• 原子性：最小操作单位，要么成功，要么失败。
• 持久性：提交或回滚后，会持久保存数据。
• 隔离性：多个事务间，相互独立。
• 一致性：事务操作前后，数据总量不变。

DCL数据库用户管理

用户的基本操作

// 创建用户：格式 create user ‘用户名’@‘主机名’ identified by ‘密码’;
create user 'zhangsan'@'%' identified by '123456';

// 删除用户：格式 drop user ‘用户名’@‘主机名’
drop user 'zhangsan'@'%';

// 修改密码：
  // 1.已经登录到数据库：
    // 格式1 update user set password = password('新密码') where user = '用户名';
    update user set password = password('abc') where user = 'zhangsan';
    // 格式2：set password for '用户名'@'主机名' = password('新密码') 
    set password for 'zhangsan'@'%' = password('123')
  // 2.忘记root密码
    // (1)管理员运行cmd ： net stop mysql  （停止mysql服务）
    // (2)使用无验证方式启动mysql ：mysql --skip-grant-tables  （cmd会卡住）
    // (3)打开新的cmd窗口：mysql  （直接回车就能登录）
    // (4)新cmd窗口：use mysql;
    // (5)新cmd窗口：update user set password=password('123456') where user= 'root';
    // (6)修改密码成功后，关闭两个cmd窗口。
    // (7)打开任务管理器，关闭进程：mysqld.exe。
    // (8)cmd：net start mysql ，启动mysql服务后，就可以正常登录了

用户的权限管理：

// 查看用户权限：show grants for '用户名'@'主机名'；
show grants for 'zhangsan'@'%'；

// 授权：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'；
grant select,update,insert on test.user to 'zhangsan'@'%';
grant all on *.* to 'zhangsan'@'%';

// 撤销权限：revoke 权限列表 on 数据库名.表名 to '用户名'@'主机名'；
revoke delete,drop on test.user to 'zhangsan'@'%';