MYSQL的SQL语句

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/SIMBA1949/article/details/82916739

MySQL

MYSQL的SQL语句

  • SQL: Structrued Query Langauage 结构化的查询语言

  • DDL(Data Definition Language): 数据定义语言 : 定义数据库或者表结构. alter(修改) create(创建) drop(删除) truncate(截断)

  • DML(Data Manipulation Language): 数据操纵语言: 用来操作表中数据: insert(插入) update(更新) delete(删除)

  • DQL(Data Query Language): 数据查询语言: 用来查询数据 select(选择) from(从哪里查) where(哪一条,判断条件)

  • DCL(Data Control Language): 数据控制语言: 用来控制用户权限 grant(授权) revoke(取消授权)

Database

-- 创建数据库
create database 数据库名 [character set 字符集 collate 校对规则];
-- 删除数据库
drop database 数据库名;
-- 修改数据的字符集
alter database 数据库名 character set 字符集;
-- 查询所有数据名
show databases;
-- 查询数据库创建语句
show create database 数据库名;
-- 查看数据库定义的语句: 查询的是数据库创建的语句
show create database 数据库名;
-- 使用数据库
use 数据库名;
-- 查看当前数据库名
select database();

Table

-- 创建表
create talbe 表名(
	列名 列的类型 [列的约束] [默认值],
    ...
    列名 列的类型 [列的约束] [默认值]
);

列的类型:
    **数字类型**
    整数: tinyint(1字节)、smallint(2)、mediumint(3)、int(4)、bigint(8)
    浮点数: float(4)、double(8)、
    	   decimal(M,D)(M+2字节)(一共M位数,小数点占D位)
    位类型:bit(M)(M取值为1~64)(1~8字节)
    日期和时间: date、time、datetime、timestamp、year
    **字符串类型**
    字符串: char(M)、varchar(M)
    文本: tinytext、text、mediumtext、longtext
	二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob	
	
列的约束
	主键约束: primary key不能重复,不能为空  通常用在编号ID auto_increment
	唯一约束: unique 唯一约束: 不能重复,但是可以为空
	非空约束: not null 不能为空
-- 删除表
drop table 表名;
-- 修改表名
rename table 旧表名 to 新表名;
rename table 旧表名1 to 新表名1,旧表名2 to 新表名2...
alter table 旧表名 rename 新表名;
-- 修改表的字符集
alter table 表名 character set 字符集;
-- 查询数据库中所有表名
show tables;
--  查看表的创建过程,建表语句
show create table 表名
-- 查询表中字段信息
desc 表名;

column

-- 添加列
alter table 表名 add 列名 列的类型 列的约束;
-- 删除列
alter table 表名 drop 列名;
-- 修改列的类型
alter table 表名 modify 列名 列的类型 [列的约束];
-- 修改列名
alter table 表名 change 旧列名 新列明 [新列的类型 新列的约束];

数据

-- 增
insert into 表名(列名1,列名2...) values(列1值,列2值...);
-- 插入多个值
INSERT INTO t_usr (username,password) 
VALUES
('李白','11'),
('杜甫','22');
-- 插入检索数据,主键冲突会插入失败
INSERT INTO t_user (username,password) 
SELECT username,password from t_usr;
-- 全表复制
create table t_user2 as select * from t_user;

-- 根据条件删除
delete from 表名 where 列名1 = 列1值 and 列名2 = 列2值...
-- 删除所有数据(delete 删除数据可以恢复,truncate 删除数据不能恢复)
delete from 表名;
-- 限制要删除的行数;注意:表中的行顺序未指定,使用LIMIT子句时,应始终使用ORDER BY子句,不然删除的记录可能不是你所预期的那样。
delete from 表名 order bu 列名... limit row_count;

-- 改,如果没有where条件语句会更新全表对应的字段
update 表名 set 列名1 = 列1值,列名2 = 列2值...[where 列名n = 列n值];
-- 查
select 列名1,列名2... from 表名;
-- 使用别名查询
select 列名1 as 列1别名,列名2 列2别名... from 表名;

视图

-- 创建视图
create view 视图名 as SQL查询语句
create view view_name as select * from t_user;
-- 删除视图
drop view 视图名;
-- 修改视图
alter view 视图名 as SQL查询语句
alter view t_v_usr as select * from t_user
-- 查看视图结构
desc 视图名;
-- 查看视图字符集
show create view 视图名;

存储过程*

-- 存储过程创建
create procedure 存储过程名(IN 入参名 入参类型... OUT 出参 出参类型...)
begin
	-- SQL语句 --
end
-- 存储过程调用
call 存储过程名(IN 入参名 入参类型... OUT 出参 出参类型...)

MySQL 约束类型

约束类型 primary key unique not null foreign key
外键约束,必须是另一张表的主键

创建表时

create table t_user2(
id int primary key auto_increment comment "主键约束",
username varchar(20) unique comment "唯一约束",
password varchar(20) not null comment "非空约束",
tid int comment "外键约束",
foreign key(tid) references t_user(id) 
);

创建表后

主键约束

-- 添加主键
alter table 表名 add primary key(列名);
-- 例如:alter table t_user add primary key(id);
-- 删除主键
alter table 表名 drop primary key;
-- 例如:alter table t_user drop primary key;

-- 添加自增
alter table 表名 modify 列名 int auto_increment;
-- 示例:alter table t_user modify id int auto_increment; 
-- 删除自增
alter table 表名 change 列名 列名 int ;
-- 示例:alter table t_user change id id int ;

唯一约束

-- 添加唯一约束
alter table 表名 add unique(列名);
-- 示例:alter table t_user add unique(username);
alter table t_user modify 列名 列的类型 列的约束;
-- 示例:alter table t_user modify username varchar(20) UNIQUE;

-- 删除唯一约束
alter table 表名 drop index 列名;
-- 示例:alter table t_user drop index username;

外键约束

-- 添加外键约束
alter table 表名 add constraint 外键名 foreign key(外键约束的列名) references 表2名(表2主键)
-- 示例:
alter table t_user5 add constraint t_user5_t_usr foreign key(tid) references t_usr(id);
-- 删除外键
alter table 表名 drop foreign key 外键名;
-- 示例:
alter table t_user5 drop foreign key t_user5_t_usr;

非空约束

-- 添加非空约束
alter table 表名 modify 列名 列的类型 not null;
-- 示例:
alter table t_user5 modify password varchar(20) not null;

-- 取消非空约束
alter table 表名 modify 列名 列的类型 null;
-- 示例:
alter table t_user5 modify password varchar(20) null;

MySQL 常用函数

-- bin() 函数表示显示二进制数
select bin(列名) from 表名;
-- hex() 函数表示显示十六进制数
select hex(列名) from 表名;
-- now() 函数表示获取当前日期和时间
insert into 表名(列名) values(now());
-- curdate() 函数表示获取当前日期
-- curtime() 函数表示获取当前时间
-- concat(s1,s2...sn) 表示字符串连接
select * from 表名 where 列名 like concat(s1,s2...sn);
-- if(value,t,f) 表示value为真,返回t、否则返回f
-- ifnull(value1,value2) 表示value1不为空,返回value1,否则返回value2
-- case when value then result else default end;表示 value 为真,返回result,否则返回 default
-- case expr when value then result else default end;表示 expr 等于value,返回result,否则返回 default
-- md5(str):返回md5加密后数据

查询

-- having和where区别在于,where在聚合之前就对记录过滤,having在聚合之后对记录过滤。建议尽可能用where过滤
##### SQL语句编写顺序及查询顺序
select ... From .. where .. group by ... having ...order by...limit offset,length;
- SQL执行顺序
  1,先from去查找表
  2,根据where的条件过滤
  3,where条件过滤完之后再group by分组
  4,having是对分组的数据进行过滤
  5,执行select查询
  6,order by排序 
  7,限制返回行的数量

条件查询

select * from 表名 where 条件1 and 条件2...

排序查询

-- desc 表示降序,asc 表示升序,默认升序
select * from 表名 order by 列名 desc|asc;

分页查询

-- startNum 从第几条记录开始查询,size 表示查询多少个
select * from 表名 limit startNum,size;

分组查询

-- 根据哪些列进行分组
select * from 表名 group by 列名1[,列名2..];

聚合函数

count():记录数
sum():求和
max():最大值
min():最小值
avg():求平均值

去重查询

-- 多列去重
select distinct 列名1,列名2... from 表名;

运算查询

算数运算符

+、-、*、/、%

select 列名 * 0.8 as 别名 from 表名;

比较运算符

=、<>或者!=、<=>(安全的等于)、<、<=、>、>=、between…and…(存在于指定范围)、

in(存在于指定集合)、is null、is not null、like、rlike(正则表达式匹配)

select * from 表名 where 列名 = 列值...

逻辑运算符

not 或者 !(逻辑非)、and 或者 &&(逻辑与)、or 或者 ||(逻辑或)、xor(逻辑异或)


位运算符

&(位与)、|(位或)、^(位异或)、~(位取反)、>>(位右移)、<<(为左移)


显示二进制、十六进制数据

-- bin() 函数显示二进制函数
select bin(列名) from 表名;
-- hex() 函数显示十六进制
select hex(列名) from 表名;

多表联查

数据库五大约束

主键约束: primary key 唯一 / 非空
唯一约束 : unique
非空约束 : not null
检查约束: mysql里面不支持 check
外键约束: foreign key

主键特性:

  • 任意两行都不具有相同的主键值
  • 每一行都必须具有一个主键值
  • 主键列中的值不允许修改或者更新
  • 主键值不能重用(如果某行从表中删除,他的主键不能赋给以后的新行)

交叉连接查询(内连接不带where条件)

select * from 表1 cross join 表2;
或者
select * from 表1,表2

内连接查询

select * from 表1 inner join 表2 on 表1.关系列 = 表2.关系列
或者
select * from 表1 join 表2 on 表1.关系列 = 表2.关系列
或者
select * from 表1,表2 where 条件

外连接查询

左外连接

select * from 表1 left outer join 表2 on 表1.关系列 = 表2.关系列

右外连接

select * from 表1 right outer join 表2 on 表1.关系列 = 表2.关系列

子查询

作为子查询的select语句只能是查询单列的SQL

组合查询

使用 UNION

  • UNION 必须有两条或者两条以上的 SELECT 语句组成,语句之间用关键字 UNION 分隔
  • UNION 中的每个查询必须包含相同的列、表达式或者聚合函数
  • 列数据类型必须兼容

UNION 会自动去除重复的行,如果不想去除可以使用 UNION ALL


DATA

员工信息表

-- 员工信息表
CREATE TABLE emp(
	empno INT comment "员工编号", 
    ename VARCHAR(50) comment "员工姓名",
    job VARCHAR(50) comment "员工工作",
    mgr	INT comment "员工经理编号",
    hiredate DATE comment "员工入职日期",
    sal	DECIMAL(7,2) comment "员工工资",
    comm DECIMAL(7,2) comment "员工奖金",
    deptno INT comment "员工所处的部门编号"
)comment "员工信息表";
阅读更多
换一批

没有更多推荐了,返回首页