SQL语句
文章目录
DDL语句
- 数据定义语言:简称【DDL】(Data Definition Language),用来定义数据库对象:数据库,表,列
等。关键字:create,alter,drop等
数据库操作:database
创建数据库
create database 数据库名;
create database 数据库名 character set 字符集;
查看数据库
- 查看数据库服务器中的所有的数据库
show databases;
- 查看某个数据库的定义的信息
show create database 数据库名;
删除数据库
慎用
drop database 数据库名称;
其他数据库操作命令
- 切换数据库
use 数据库名;
- 查看正在使用的数据库
select database();
表操作:table
创建表
create table 表名(字段名1 类型(长度) 约束, 字段名2 类型(长度) 约束, ......);
- 单表约束
- 主键约束:primary key
- 唯一约束:unique
- 非空约束:not null
主键约束 = 唯一约束 + 非空约束
- 例如:
#商品表 CREATE TABLE product ( pid INT PRIMARY KEY AUTO_INCREMENT, # 自增加 AUTO_INCREMENT pname VARCHAR(20),#商品名称 price DOUBLE, #商品价格 pdate DATE, # 日期 cid int #分类ID ); #目录表 create table category( id INT PRIMARY KEY , cname varchar(100) );
查看表
- 查看数据库中的所有表
show tables;
- 查看表结构
desc 表名;
删除表
drop table 表名;
修改表
- 添加列
alter table 表名 add 列名 类型(长度) 约束;
- 修改列的类型长度及约束
alter table 表名 modify 列名 类型(长度) 约束;
- 修改列名
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
- 删除列
alter table 表名 drop 列名;
- 修改表名
rename table 表名 to 新表名;
- 修改表的字符集
alter table 表名 character set 字符集;
DML语句
- 数据操作语言:简称【DML】(Data Manipulation Language),用来对数据库中表的记录进行更
新。 - 关键字:insert,delete,update等
插入记录:insert
- 向表中插入某些列
insert into 表 (列名1,列名2,列名3..) values (值1,值2,值3..);
- 向表中插入所有列
insert into 表 values (值1,值2,值3..);
- 从一个表中选定的列插入到目标表中
insert into 目标表 (列名1,列名2,列名3..) values select (列名1,列名2,列名3..) from 来源表;
- 将一个表中所有列插入到目标表中
insert into 目标表 values select * from 来源表
注意:
- 列名数与values后面的值的个数相等
- 列的顺序与插入的值得顺序一致
- 列名的类型与插入的值要一致.
- 插入值得时候不能超过最大长度.
- 值如果是字符串或者日期需要加引号(一般是单引号)
- 例如:
INSERT INTO product VALUES(NULL,'泰国大榴莲', 98, NULL, 1); INSERT INTO product VALUES(NULL,'泰国大枣', 38, NULL, 1); INSERT INTO product VALUES(NULL,'新疆切糕', 68, NULL, 2); INSERT INTO product VALUES(NULL,'十三香', 10, NULL, 2); INSERT INTO product VALUES(NULL,'泰国大枣', 20, NULL, 2); INSERT INTO category VALUES(1,'国外食品'); INSERT INTO category VALUES(2,'国内食品');
更新记录: update
update 表名 set 字段名=值,字段名=值;
update 表名 set 字段名=值,字段名=值 where 条件;
注意:
- 列名的类型与修改的值要一致
- 修改值不能超过最大长度
- 值如果是字符串或者日期需要加引号
删除记录:delete
delete from 表名 [where 条件];
注意:
- 删除表中所有记录使用【delete from 表名】,还是用【truncate table 表名】?
- 删除方式:
- delete :一条一条删除,不清空auto_increment记录数。
- truncate :直接将表删除,重新建表,auto_increment将置为零,从新开始。
DCL语句
- 数据控制语言:简称【DCL】(Data Control Language),用来定义数据库的访问权限和安全级别,及创建用户;
- 关键字:grant等
用户管理
创建用户
- 创建用户,在所有IP都能登录数据库
CREATE USER 'username'@'%' IDENTIFY BY 'password'
- 例如:
CREATE USER 'xiaochen'@'%' IDENTIFY BY '123456';
- 创建用户,只有指定IP才能登录数据库
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
- 例如:
CREATE USER 'xiaochen'@'223.88.93.55' IDENTIFY BY '123456';
修改用户密码
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';
删除用户
DROP USER 'username'@'hostname';
权限管理
用户授权
- 给指定用户授予所有数据库的所有权限
GRANT ALL ON *.* TO 'username'@'hostname';
- 给指定用户授予所有数据库的指定权限
GRANT privileges ON *.* TO 'username'@'hostname';
privileges 包括:SELECT、INSERT、UPDATE、DELETE等
- 给指定用户授予指定数据库的指定权限
- 某一库的所有表
GRANT privileges ON database.* 'username'@'hostname';
- 某一库的某一张表
GRANT privileges ON database.table 'username'@'hostname';
- 某一库的所有表
查看用户权限
SHOW GRANTS FOR 'username'@'hostname';
撤销用户权限
REVOKE privileges ON database.* FROM 'username'@'hostname';
REVOKE privileges ON database.table FROM 'username'@'hostname';
刷新权限
FLUSH PRIVILEGES;
不具备管理员权限操作用户
use mysql;
INSERT INTO user(host,user,password,select_priv,insert_priv,update_priv) VALUES('%','test',password('123456'),'y','y','y');
flush privileges;
DQL语句
- 数据查询语言:简称【DQL】(Data Query Language),用来查询数据库中表的记录。
- 关键字:select,from,where等
- 完整语法顺序:
SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT < limit_number >
简单查询
- 查询所有信息
select * from 表名;
- 查询某几列信息
select 列名,列名 from 表名;
- 别名查询,使用的as关键字,as可以省略的
select * from 表名 as 别名;
select 列名 as 别名 from 表名;
- 去掉重复值
select distinct 列名 from 表名;
- 查询结果是表达式(运算查询)::将所有商品的价格+10元进行显示
select pname, price+10 from product;
条件查询
select * from 表名 where 条件;
- 例如:
- 查询商品名称为十三香的商品所有信息
select * from product where pname = '十三香';
- .查询商品价格>60元的所有的商品信息
select * from product where price > 60;
- 查询商品名称为十三香的商品所有信息
条件写法
>,<,=,>=,<=,<>
like
使用占位符 _ 和 % _代表一个字符 %代表任意个字符.select * from product where pname like '%新%';
- in在某个范围中获得值(exists).
select * from product where pid in (2,5,8);
常用条件
类型 | 条件 | 寓意 |
---|---|---|
比较运算符 | >、< 、<= 、>= 、= 、<> | 大于、小于、小于等于、大于等于、不等于 |
比较运算符 | BETWEEN …AND… | 显示在某一区间的值(含头和尾) |
比较运算符 | IN(set) | 显示在in列表中的值,例:in(100,200) |
比较运算符 | LIKE ‘%张%’ | 模糊查询,%代表任意字符,_代表一个字符 |
比较运算符 | IS NULL | 判断是否为空 |
逻辑运算符 | and | 多个条件同时成立 |
逻辑运算符 | or | 多个条件任一成立 |
逻辑运算符 | not | 条件不成立 |
排序
- 关键字:ORDER BY ASC/DESC
- 按某列值查询排序,asc-升序,desc-降序
select * from 表名 order by 列名;
- 例如:
select * from product order by price;
select * from product where pname like '%新%' order by price desc;
聚合函数
- 特点:只对单列进行操作
- 常用的聚合函数
- sum():求某一列的和
- avg():求某一列的平均值
- max():求某一列的最大值
- min():求某一列的最小值
- count():求某一列的元素个数
- 例如:
select sum(price) from product;
select avg(price) from product;
select count(*) from product;
分组
- 关键字:GROUP BY …HAVING
- 例如:
- 根据cid字段分组,分组后统计商品的个数
select cid,count(*) from product group by cid;
- 根据cid分组,分组统计每组商品的平均价格,并且平均价格> 60;
select cid,avg(price) from product group by cid having avg(price)>60;
- 根据cid字段分组,分组后统计商品的个数
注意:
- select语句中的列(非聚合函数列),必须出现在group by子句中
- group by子句中的列,不一定要出现在select语句中
- 聚合函数只能出现select语句中或者having语句中,一定不能出现在where语句中。
分页查询
- 关键字:LIMIT [offset,] rows
- LIMIT 关键字不是 SQL92 标准提出的关键字,它是 MySQL 独有的语法。
- 通过 limit 关键字, MySQL 实现了物理分页
分页分为逻辑分页和物理分页:
- 逻辑分页:将数据库中的数据查询到内存之后再进行分页。
- 物理分页:通过LIMIT关键字,直接在数据库中进行分页,最终返回的数据,只是分页后的数据。
- 格式:
SELECT * FROM table LIMIT [offset,] rows;
- offset :偏移量
- rows :每页多少行记录。
子查询
- 子查询允许把一个查询嵌套在另一个查询当中
- 子查询,又叫内部查询,相对于内部查询,包含内部查询的就称为外部查询
- 子查询可以包含普通select,可以包括的任何子句,比如:distinct、 group by、order by、limit、
join和union等; - 但是对应的外部查询必须是以下语句之一:select、insert、update、delete
- 位置:
- select中、from 后、where 中
- group by 和order by 中无实用意义
其他查询
- union 集合的并集(不包含重复记录)
- unionall 集合的并集(包含重复记录)