mysql的通用语法

最新推荐文章于 2024-08-29 19:21:48 发布

初来人间不知苦，下辈子不来了

最新推荐文章于 2024-08-29 19:21:48 发布

阅读量903

点赞数 23

文章标签： mysql oracle 数据库

本文链接：https://blog.csdn.net/m0_67069500/article/details/135686093

版权

SQL的通用语法

SQL分类

1. SQL语句可以单行或多行书写，以分号结尾。

2. SQL语句可以使用空格/缩进来增强语句的可读性。

3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

4. 注释：

• 单行注释：-- 注释内容或 # 注释内容(MySQL特有)

• 多行注释： /* 注释内容 */

DDL-数据库操作

查询所有数据库

show databases;

查询当前数据库

select database();

数据库的创建

create database [ if not exists] 数据库名 [default charset 字符集] [collate 排序规则]；

数据库的删除

drop database [if exists]数据库名；

数据库的使用（切换）

use 数据库名；

DDL-表操作-查询

查询当前数据库所有表

show tables;

查询表结构

desc 表名；

查询指定表的建表语句

show create table 表名；

例子：

注意：[ ... ] 为可选参数，最后一个字段后面没有逗号

DDL-表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

字符串类型

数值类型

日期时间类型

例子

根据需求创建表(设计合理的数据类型、长度)

设计一张员工信息表，要求如下：

1. 编号（纯数字）

2. 员工工号 (字符串类型，长度不超过10位)

3. 员工姓名（字符串类型，长度不超过10位）

4. 性别（男/女，存储一个汉字）

5. 年龄（正常人年龄，不可能存储负数）

6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）

7. 入职时间（取值年月日即可）

创建表

des

DDL-表操作-修改

表结构添加字段

alter table表名add 字段名类型（长度）[comment注释] [约束]

案例

为emp表增加一个新的字段”昵称”为nickname，类型为varchar(20)

alter table emp add nickname varchar(20) comment "昵称";

修改表的数据类型

alter table 表名 modify 字段名新数据类型（长度）；

修改表的字段名和字段类型

alter table 表名 change 旧字段名新字段名类型（长度）[comment注释] [约束]；

案例：

将emp表的nickname字段修改为username，类型为varchar(30)

alter table emp change nickname username varchar(30) comment "昵称";

删除表的字段

alter table 表名 drop 字段名；

案例

将emp表的字段username删除

alter table emp drop username;

修改表名

alter table 表名 rename 新表名；

案例

将emp表的表名字修改为employee

alter table emp rename to employee;

删除表

drop table [if exists] 表名；

删除指定表，并重新创建该表

truncate table 表名；

注意：在删除表时，表中的全部数据也会被删除。

总结：

DDL-数据库操作

show database；

create database 数据库名；

select databases（）；

drop database 数据库名；

DDL-表操作

show tables;

create table 表名（字段字段类型字段字段类型)；

desc 表名；

alter table 表名 add/modify/change/drop/rename do ;

drop table 表名；

MySQL图形化界面

DML-介绍

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作

添加数据（INSERT）

修改数据（UPDATE）

删除数据（DELETE）

DML-添加数据

给指定字段添加数据

insert into 表名（字段名1，字段名2，..） values (值1，值2)；

给全部字段添加数据

insert into 表名 values (值1，值2...);

批量添加数据

insert into 表名（字段名1.字段名2) values (值1，值2...),(值1，值2...),(值1，值2...); insert into 表名 values (值1，值2,..),(值1，值2,..),(值1，值2,..);

注意：

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。

• 字符串和日期型数据应该包含在引号中。

• 插入的数据大小，应该在字段的规定范围内。

DML-修改数据

update 表名 set 字段名1 =值1，字段名2=值2,...[where 条件]；

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

DML-删除数据

delete from 表名 [where 条件]

注意：

• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

•DELETE 语句不能删除某一个字段的值(可以使用UPDATE)。

总结

DQL-介绍

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

查询关键字：SELECT

DQL-基本查询

#数据准备 create table emp( id int comment '编号', workno varchar(10) comment '工号', name varchar(10) comment '姓名', gender char comment '性别', age tinyint unsigned comment '年龄', idcard char(18) comment '身份证', workaddress varchar(50) comment '工作地址', entrydate date comment '入职时间' ) comment "员工表";

insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (1, '1', '柳一岩', '女', 20, '12345878912345678', '北京', '2022-1-5'), (2, '2', '柳二岩', '男', 18, '12745678912345678', '上海', '2022-3-5'), (3, '3', '柳三岩', '女', 38, '12345878912345678', '北京', '2022-4-5'), (4, '4', '柳四岩', '男', 88, '12345678912345678', '上海', '2022-5-5'), (5, '5', '柳五岩', '男', 20, '12645678912345678', '北京', '2022-6-5'), (6, '6', '柳留岩', '女', 27, '12345678912345678', '上海', '2022-7-5'), (7, '7', '柳我岩', '男', 28, '16345678912345678', '北京', '2022-3-5'), (8, '8', '柳你岩', '女', 29, '12945678912345678', '上海', '2022-8-5'), (9, '9', '柳是岩', '男', 23, '12945678912345678', '北京', '2022-9-5'), (10, '10', '柳符岩', '女', 43, '12045678912845678', '上海', '2022-7-5'), (11, '11', '柳人岩', '男', 87, '12395688912345678', '上海', '2022-9-7'), (12, '12', '柳哦岩', '女', 62, '12345878912345678', '北京', '2021-5-5'), (13, '13', '柳个哦', '女', 25, '12345078912345678', '东京', '2033-3-5'), (14, '14', '柳个好', '女', 33, '12345574912345678', '西京', '2015-3-5'), (15, '15', '柳个符', '女', 55, '12345578912645678', '南京', '2014-3-5'), (16, '16', '柳个的', '女', 11, 'null', '北京', '2012-3-5');

查询多个字段

select 字段1,字段2,字段3，...from 表名；

查询所有字段

select * from 表名；

设置别名

select 字段1 [as 别名1]，字段2[as 别名2] ... from 表名；

去除重复记录

select distinct 字段列表 from 表名；

注意 : * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。

实例：

1.查询指定字段 name,workno,age 返回

2.查询所有字段返回

或者select * from emp；但是在生产上尽量不要用*，影响效率和数据过大会造成卡机

3.查询所有员工的工作地址，起别名

as可以省略

4.查询公司员工的上班地址（不要重发）

DQL-条件查询

select 字段列表 from 表名 where 条件列表;

条件查询例子：

1.查询年龄等于88的员工

select * from emp where age = 88;

2.查询年龄小于20的员工信息

mysql> select * from emp where age < 20;

3.查询年龄小于等于20的员工信息

select * from emp where age

4.查询没有身份证号的员工

select * from emp where idcard is null;

5.查询有身份证号的员工信息

6.查询年龄不等于88的员工信息

select * from emp where age != 88;

select * from emp where age <> 88;

7.查询年龄在15（包含）到20岁的（包含）之间的员工信息

或者把上面的语句的 &&改成为and结果是一样的

或select * from emp where age between 15 and 20;

8.查询性别为女且年龄小于25岁员工信息

select * from emp where gender = '女' and age < 25;

9.查询年龄等于18或20或40等员工信息

select * from emp where age = 18 or age = 20 or age =40;

select * from emp where age in(18,20,40);

10.查询姓名为两个字的员工信息

select * from emp where name like "_ _";

11.查询身份证号最后一位是x的员工信息

select * from emp where idcard like '%x';

select * from emp where idcard like '-----------------x'; (17个下滑线)

DQL-聚合函数

介绍

将一列数据作为一个整体，进行纵向计算。

常见的聚合函数

语法

select 聚合函数（字段列表）from 表名;

注意：null值不能参与所有聚合函数运算。

例子：

1.统计该企业员工数量

select count(idcard) from emp;

2.统计该企业的员工的平均年龄

select avg(age) from emp;

3.统计该企业的员工的最大年龄

select max(age) from emp;

4.统计该企业的员工的最小年龄

select min(age) from emp;

5.统计西安地区员工的年龄之和

select sun(age) from emp where workaddress = '西安';

DQL-分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

例子：

1.根据性别分组，统计男性员工和女性员工的数量

select gender, count(*) from emp group by gender;

2.根据性别分组，统计男性员工和女性员工的平均年龄

select gender, avgt(age) from emp group by gender;

3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

select workaddress, count(*) from emp where age < 45 group by workaddress having count(*) >= 3;

2. where与having区别➢ 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

➢ 判断条件不同：where不能对聚合函数进行判断，而having可以。

注意

• 执行顺序: where > 聚合函数 > having 。

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

DQL-排序查询

select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；

2. 排序方式

➢ ASC：升序（默认值）

➢ DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

例子：

1.根据年龄对公司的员工进行升序排序

select * from emp order by age asc;

2.根据入职时间，对员工进行降序排序；

select * from emp order by entrydate desc;

3.根据年龄对公司的员工进行升序，年龄相同，在按照入职时间进行降序排序

select * from emp order by age asc ,entrydate desc;

DQL-分页查询

select 字段列表 from 表名 limit 起始索引，查询记录数；

注意

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

例子：

1.查询第1页的员工数据，每页展示10条记录

select * from emp limit 0,10; #(第一页是为0开始的)

select * from emp limit 10;

2.查询第2页员工数据，每页展示10条记录

select * from emp limit 1,10;

案例

按照需求完成如下DQL语句编写

1. 查询年龄为20,21,22,23岁的女性员工信息。

select * from emp where gener = ''女' and age in(20,21,22,23);

2. 查询性别为男，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。

select * from emp where gender = ''男' and age between 20 and 40 and name like '---';

3. 统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。

select gender, count(*) from emp where age < 60 group by gender;

4. 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。

select age,name from emp where age

5. 查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

select * from emp where gender = '男'and age between 20 and 40 order by age asc， entrydate asc limit 5;

（先分组后分页）

DQL-执行顺序

例子：

查询年龄大于15的员工的姓名，年龄，并根据年龄进行升序排序

select name ,age from emp where age > 15 order by age asc;

DCL-介绍

DCL英文全称是 Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

DCL-管理用户

1.查询用户

use mysql; select * from user;

2.创建用户

create user '用户名'@'主机名 ' idetified by '密码'；

3.修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '密码';

4.删除用户

drop user '用户名'@'主机名'

注意

主机名可以使用%通配。

这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用。

例子：

1.创建用户itcast,只能在当前主机localhost访问，密码123456；

create user 'itcast'@'localhost' identified by '123456';

2.创建用户heima，可以在任意主机访问该数据，密码123456；

create user 'itcast'@'%' identified by '123456';

3.修改用户heima的访问密码为1234；

alter user 'heima'@'%' identified with mysql_native_password by '1234';

4.删除itcast@localhost用户

drop user 'itcast'@'localhost';

DCL-控制权限

MySQL中定义了很多种权限，但是常用的就是以下几种。

其他权限描述及含义，可以直接参考官方文档。

MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL

DCL-权限控制

1. 查询权限

show grants for '用户名'@ '主机名'；例如： grant all privileges on *.* to 'root'@'%' identified by 'Gipex!#@2023070803146'; flush privileges; select user,user,host,authentication_string from mysql.user;

例子：查询黑马的权限

2. 授予权限

grant 权限列表 on 数据库.表名 to '用户名'@'主机名'；

(all所有表的权限)

gran all on itcast .* to "heima"@'heima'

3. 撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'

revoke all on itcast.* from 'heima'@'%';

注意：

• 多个权限之间，使用逗号分隔

• 授权时，数据库名和表名可以使用 * 进行通配，代表所有

privilegesCode 表示授予的权限类型, 常用的有以下几种类型[1] all privileges: 所有权限 select: 读取权限 delete: 删除权限 update: 更新权限 create: 创建权限 drop: 删除数据库、数据表权限 dbName.tableName 表示授予权限的具体库或表, 常用的有以下几种选项 .: 授予该数据库服务器所有数据库的权限 dbName.*: 授予dbName数据库所有表的权限 dbName.dbTable: 授予数据库dbName中dbTable表的权限 username@host 表示授予的用户以及允许该用户登录的IP地址. 其中Host有以下几种类型 localhost: 只允许该用户在本地登录, 不能远程登录 %: 允许在除本机之外的任何一台机器远程登录 192.168.52.32: 具体的 IP 表示只允许该用户从特定IP登录. password 指定该用户登录时的密码 flush privileges 表示刷新权限变更

修改密码

运行以下命令可以修改用户密码:

update mysql.user set password = password('zhangsannew') where user = 'zhangsan' and host = '%'; flush privileges;

删除授权用户在表中的数据：

运行以下命令可以删除用户:

drop user zhangsan@'%';

例子：