SQL 基本命令

DDL -- Data Definition Language

DDL - 操作数据库

显示所有数据库

show databases;

创建数据库

create database test;
--如果不存在则创建
create database if not exists test;
--带字符集，主要不要设置为utf8,因为utf8的字符长度为3个字节，但是有的需要为4个字节，设置为utf8mb4
create database test default charset urf8mb4;

删除数据库

--数据库存在则删除成功，如果不存在会报错
drop database test;
 
drop database if exists test;

使用数据库

use test;

查看当前在哪个数据库

select database();

DDL - 操作表 - 查询

note：下列指令中的test，均为表名。

 查询所有的表

show tables;

查询表结构

desc test;

查询建表语句

show create table test;

DDL - 表操作 - 创建

CREATE TABLE tb_user(
    id INT COMMENT '编号',
    NAME VARCHAR(20) COMMENT '姓名',
    age INT COMMENT '年龄',
    gender VARCHAR(1) COMMENT '性别'
)COMMENT '用户表';

 DDL- 表操作 - 数据类型

数值类型

例如age不会出现负数，并且使用int会浪费字节，TINYINT足够则，类型应该为 

age TINYINT UNSIGNED

字符串类型

在长短相同的情况下char 比 varchar的性能好

 Char是固定长度，例如char(10),如果存储数据长度为1，也会填充至长度为10

Varchar是边长，例如varchar(10)，如果存储长度为1，则长度占用是1。

那在相同长度的情况下，为什么varchar性能低于char因为是变长则需要动态计算实际占用的长度

实际选择:

当需要存储username，但是长度是不定的可能是1位，也可能是10位。则此时选择varchar更优。

如果长度已经确定，例如性别字段，不是男就是女，要不就是外星人。所以长度固定为1，选择char(1)为最优选择。

时间日期类型

 案例

CREATE TABLE tb_employee(
    id INT COMMENT '编号',
    workno VARCHAR(10) COMMENT '工号',
    NAME VARCHAR(10) COMMENT '姓名',
    gender CHAR(1) COMMENT '性别',
    age TINYINT UNSIGNED COMMENT '年龄',
    idcard CHAR(18) COMMENT '身份证号',
    entry_date DATE COMMENT '入职时间'
) COMMENT '员工信息表';

 DDL - 表操作 - 修改

增加字段

 

ALTER TABLE tb_employee ADD nickname VARCHAR(20) COMMENT '昵称';

修改 字段名、数据类型 

ALTER TABLE tb_employee CHANGE nickname username VARCHAR(30) comment '用户名';

 删除字段

alter table tb_employee drop username;

 修改表名

ALTER TABLE tb_employee RENAME TO employee;

DDL - 表操作 - 删除

#删除表
DROP TABLE IF EXISTS tb_user;
 
#删除并重新创建该表
truncate table tb_employee;

DDL - 总结

 DML - Data Manipulation Language

 Insert

 

#指定字段插入
insert into tb_employee(id, workno, name, gender, age, idcard, entry_date) values (1,'1','lisi','男',10,'123456789012345678','2000-01-01');
 
#全自动插入
insert into tb_employee values (2,'2','wangwu','男',11,'123456789012345678','2000-01-01');
 
#批量插入
insert into tb_employee values (3,'3','zhaoliu','男',11,'123456789012345678','2000-01-01'),(4,'4','wangmazi','男',11,'123456789012345678','2000-01-01')

 update

#单个字段更新
update tb_employee set name = '李四' where id = 1;
#多字段更新
update tb_employee set name = 'lisi', gender = '女' where id = 1;
#全表更新
update tb_employee set entry_date = '2008-01-01' ;

 delete

 

delete from tb_employee where gender = '女';
#删除全表
delete from tb_employee;

总结

 DQL -- Date Query Language

 

 基本查询

 

#查询指定字段
SELECT name, workno,age from emp;
#查询全部字段
select * from emp;
#查询工作地址，起别名
select workaddress as '工作地址' from emp;
#起别名 省略 as 关键字
select workaddress '工作地址' from emp;
#查询你上班地址(不要重复)
select distinct workaddress '工作地址' from emp;

条件查询

 

select * from emp where age = 88;
 
select * from emp where age < 20;
 
select * from emp where age <= 20;
 
select * from emp where idcard is null;
 
select * from emp where idcard is not null ;
 
#年龄不等88
select * from emp where age != 88;
select * from emp where age <> 88;
 
#年龄15-20 包含15和20 between 是前后包含(必须从小打大)，否则查不到数据
select * from emp where  age between 15 and 20;
select * from emp where age >= 15 and age <=20;
select * from emp where age >= 15 && age <=20;
 
select * from emp where gender = '女' and age < 25;
 
#年龄等于 18或者20或者40
select * from emp where age in (18,20 ,40);
 
#查询名字为2个字的
select * from emp where name like '__';
#select * from emp where length(name) = 2;
#查询身份证号最后一位是X的员工
select * from emp where idcard like '%X';
#select * from emp where substring(idcard, length(idcard)) = 'X';

聚合函数

select count(*) from emp;
 
#聚合函数是不包含null的，idcard有一个为null，则比count(*) 少一个
select count(idcard) from emp;
 
select avg(age) from emp;
 
select max(age) from emp;
 
select min(age) from emp;
 
#西安所有员工年龄之和
select sum(age) from emp where workaddress = '西安';

 分组查询

 

#按照性别分组，统计男女各多少员工
select gender, count(*)  from emp group by gender;
#按照性别分组，统计男女各平均年龄
select gender, avg(age) from emp group by gender;
#查询年龄小于45岁，并更具工作地址分组，获取员工数量大于等于3个工作地址
select workaddress,count(*) from emp where age < 45 group by workaddress having count(*) >=3;

 Having

#having是在分组的基础上进行的筛选条件
#执行的顺序为 1.where 先过滤 ,2.gourp by分组(同时执行聚合函数),3.having筛选
select workaddress,count(*) from emp where age < 45 group by workaddress having count(*) >=3;
#则上述sql可以分解为

1.按workaddress进行分组，查询分组过后每个组的数量

 2.在分组数量的基础上，在进行数量>=3的条件判断。

 

 排序查询

#不写默认是 asc升序
select * from emp order by age;
 
select * from emp order by entrydate desc;
 
#根据年龄对公司的员工进行升序排序,年龄相同，在按照入职之间降序排序
select * from emp order by age asc,entrydate desc ;

 分页查询

 

#查询第一页，每页10条
select * from emp limit 10; #应该是limit 0,10;但是0可以省略直接limit 10
select * from emp limit 0,10;
#查询第二页，每页10条
select * from emp limit 10,10;

 案例

 
select * from emp where age between 20 and 23;
 
select * from emp where gender = '男' and age between 20 and 40 and name like '___';
 
select gender,count(*) from emp where age < 60 group by gender;
 
select name,age from emp where age <=35 order by age asc ,entrydate desc;
 
select * from emp where gender = '男' and age between 20 and 40 order by age asc ,entrydate asc limit 5;

 执行顺序

 可以使用别名验证

select name, age from emp where age < 30 order by age;
#第一步给emp起别名e,并且where 中可以使用别名，则说明from 在 where 之前
select name, age from emp e where e.age < 30 order by age;
#第二步 select 字段 也使用表名别名引用字段,说明select 在 from的后面
select e.name, e.age from emp e where e.age < 30 order by age;
#第二步 select 字段 起别名 在where中引用，报错说明where 在 select之前
select e.name ename, e.age eage from emp e where e.age < 30 order by age;
#第二步 order by 可以使用select起的别名，则说明order by 在select 执行之后
select e.name ename, e.age eage from emp e where e.age < 30 order by eage;

 总结

 DCL - Data Control Language

 管理用户

 

 创建用户

#创建test用户只能在本机登录
create user 'test'@'localhost' identified by 'test';

创建成功后，可以在mysql库的user表中查看，可以发现此时的权限都为N，只能登录mysql，没有其他权限

 

#创建用户 global，能在任意主机访问数据库，密码test
create user 'global'@'%' identified by 'test';

修改密码

#Mysql 版本 8.0.4以后
ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test' ;
#之前, 但是需要使用FLUSH PRIVILEGES;命令使其生效，否则还是之前的密码登录，除非mysql服务重启
update user set password=password('test') where user='test';
FLUSH PRIVILEGES;

删除用户

drop user 'test'@'localhost';

权限控制

 

#查询权限
show grants for 'test'@'localhost';
 
#授予权限
#把test库所有表的全部权限给test
grant all on test.* to 'test'@'localhost';
 
#撤销权限
revoke all on test.* from 'test'@'localhost';

总结