SQL 基本命令

admin管理员组

文章数量:1794759

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- 表操作 - 数据类型 数值类型

分类	类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
数值类型	TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3 bytes	(-8388608，8388607)	(0，16777215)	大整数值
INT或INTEGER	4 bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8 bytes	(-2^63，2^63-1)	(0，2^64-1)	极大整数值
FLOAT	4 bytes	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
DECIMAL		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

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

age TINYINT UNSIGNED

字符串类型

分类	类型	大小	描述
字符串类型	CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

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

char(10) -----------> 性能好

varchar(10) ---------> 性能较差

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

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

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

实际选择:

用户名  username  varchar(10)

性别  gender  char(1)

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

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

时间日期类型

分类	类型	大小	范围	格式	描述
日期类型	DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901 至 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

 案例

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'; 总结

 

本文标签： 命令SQL