SQL基础语句_sql 之间

数据库相关指令

1.查看所有数据库

SHOW DATABASES;
1

3.创建数据库

CREATE DATABASE 数据库名;
CREATE DATABASE 数据库名 CHARSET=字符集;
示例:
CREATE DATABASE db1;
CREATE DATABASE db2 CHARSET=utf8;
1
2
3
4
5

3.查看数据库信息

SHOW CREATE DATABASE 数据库名;
1

4.删除数据库

DROP DATABASE 数据库名;
1

5.进入数据库

USE 数据库名;
1

表相关的SQL语句

1.显示所有表

SHOW TABLES;
1

2.创建表

CREATE TABLE 表名(字段1名 类型,字段2名 类型)CHARSET=utf8mb4/gbk;
示例:
create table hero(name varchar(50),money int,type varchar(10))charset=utf8;
1
2
3

某些数据库指定字符集为utf8时,默认为utf8mb3 ==>mb表示most bytes(最大字节)
utf8mp4中包含更多的表情和生僻字

3.查看表信息

SHOW CREATE TABLE 表名;
1

4.删除表

DROP TABLE 表名;
1

5.修改表名

RENAME TABLE 原名 TO 新名;
1

6.查看表字段的信息

DESC 表名;
1

7.添加表字段

在表最后面添加字段
ALTER TABLE 表名 ADD 字段名 类型;
在表最前面添加字段
ALTER TABLE 表名 ADD 字段名 类型 FIRST;
在指定字段后添加字段
ALTER TABLE 表名 ADD 字段名 类型 AFTER 指定字段名;
1
2
3
4
5
6

8.删除表字段

ALTER TABLE 表名 DROP 字段名;
1

9.修改表字段

ALTER TABLE 表名 CHANGE 字段原名 字段新名 新类型;
1

10.删除表并创建新表

#删除表并创建新表,将表之前主键自增的数据清零
truncate table 表名;
1
2

数据相关SQL

插入数据insert

1.全表插入

INSERT INTO 表名 values(值1,值2,....);
示例:
insert into person values("tom",18);
1
2
3

2.指定字段插入

INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...);
示例:
insert into person(name,age)values("jerry",20);
1
2
3

3.批量插入数据

insert into person values("aa",12),("bb",12);
insert into person(name) values("cc"),("dd"),("ee");
1
2

查询数据select

1.查询

SELECT 字段信息 FROM 表名 WHERE 条件;
1

修改数据updat

1.修改

UPDATE 表名 SET 字段名=? ,字段名=?... WHERE 条件;
1

删除数据delete

DELETE FROM 表名 WHERE 条件;
1

---

数据类型

Number类型

int 的默认值是11,包含一个符号位.我们也可以指定一个小于11的位数,利用zerofill关键字填0,示例:

create table t1(age int(5) zerofill);
insert into t1 values(18);
select * from t1;
1
2
3

Text类型

Date类型

大部分MySQL软件的timestamp类型 默认设置的为 当数据改变时时间会自动跟着改变,可以通过以下方式关闭自动更新时间

• 关闭自动更新时间:

alter table product change created created timestamp not null default current_timestamp;
1

关闭自动更新时间后字段属性:

• 打开自动更新时间:

alter table product change created created timestamp not null default current_timestamp on update current_timestamp;
1

打开自动更新后字段属性:

---

主键约束 primary key

• 约束: 创建表时给表字段添加的限制条件
• 主键: 表示数据唯一性的字段称为主键
• 主键约束: 限制主键的值唯一且非空

举例:
create table t5(id int primary key,name varchar(30));
insert into t5 values(1,"a");
insert into t5 values(2,"b");
insert into t5 values(2,"c");   //重复报错
insert into t5 values(null,"d");      //不能为null 报错  
1
2
3
4
5
6

主键约束+自增 primary key auto_increment

• 当字段设置为自增后 插入null值时会触发自增
• 自增规则: 从历史最大值+1

数据库SQL语句的分类

• DDL:数据定义语言,包括数据库相关和表相关的SQL
• DML:数据操作语言,包括:增删改查相关的语句
• DQL:数据查询语言,包括:select查询相关
• TCL:事务控制语言,包含和事务相关的SQL语句
• DCL:数据控制语言,包含创建用户以及用户权限分配相关SQL

比较运算符 < > >= <= = !=和<>

and or 和 not

is null 和 is not null

between x and y 两者之间(包含x和y) / not between x and y

in(x,y,z,…) / not in(…)

distinct 去重

concat(a,b) 将ab拼接

substring(字段名,起始角标,结束角标) 截取字段的指定数据

#表示获取title字段中的1~3个字符串,将获取到的结果和 ... 拼接
select id,concat(substring(title,1,3),'...') title,sale_count from product order by sale_count desc limit 0,6
1
2

length()

unsigned 无符号位

表示无符号位,将使得此字段不会出现负数,且取值区间是原正数的2倍

字段是否添加unsigned需要自行评估,例如表中的id字段永远不可能为负数,就可以用此关键字修饰

#id的取值范围为:[-128,127]
create table user(id tinyint)
#添加了unsigned关键字后,id的取值范围为:[0,255]
create table user(id tinyint unsigned)
1
2
3
4

comment 备注说明

create table user(id tinyint unsigned auto_increment comment '数据id')
1

if(判断条件,满足条件执行,不满足条件执行)

select id,if(length(title)>9,concat(substring(title,1,3),'...'),title) title,sale_count from product 
1

模糊查询 like

• %:代表0或多个未知字符
• _:代表1个未知字符
  

排序 order by

order by 字段名 asc(升序 默认)/desc(降序)
order by 字段名,字段名...
1
2

分页查询

limit 跳过的条数,请求的条数(每页的条数)
1

别名

select name as "别名" from emp;
select name  "别名" from emp;省略 as
select name 别名 from emp; 省略 as 和 双引号 
1
2
3

聚合函数

通过聚合函数可以对查询的多条数据进行统计查询

• 平均值avg()

#查询1号部门的平均工资
select avg(sal) from emp where dept_id = 1;
1
2

• 最大值max()
• 最小值min()
• 求和sum()
• 计数count()

select count(*) from emp where job = '程序员';
1
2

分组查询 group by

可以将某个字段相同值的数据划分为一组,然后以组为单位进行统计查询

#查询每个部门的平均工资
select dept_id,avg(sal) from emp group by dept_id;
1
2

各种查询相关的关键字顺序

select 字段信息 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数.

having关键字

where 只能写普通字段的函数,后面不能写聚合字段的函数

子查询(嵌套查询)

示例:

#查询工资最低的员工的同事们的信息(指同一部门)
#1 得到最低工资
select min(sal) from emp;
#2 通过最低工资得到拿最低工资人的部门id
select dept_id from emp where sal=(select min(sal) from emp);
#3 通过部门id查询员工
select * from emp where dept_id=(select dept_id from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
1
2
3
4
5
6
7

关联关系(表关系)

• 一对一
• 一对多
• 多对多

如何建立表关系?

• 一对一:可以在两张关系表种任何一个表里面添加建立关系的字段,指向另一个表的主键
• 一对多:在一对多的两张表里面的表示多的表中添加建立关系的字段指向另外一张表的主键
• 多对多:需要建立关系表,分别指向两张表的主键

关联查询

• 同时查询多张表数据的查询方式称为关联查询
• 关联查询包括:等值连接,内连接,外连接

等值链接

select * from A表,B表 where 关联关系 and 其他条件;
示例:
#查询工资高于2000的员工姓名,工资和部门信息.当某个字段是其中一个表特有的,那么在操作该字段时可以不用 表名.字段名
select e.name,e.sal,d.* from emp e,dept d where e.dept_id = d.id and sal >2000;
1
2
3
4

内连接

• 内连接和等值连接作用一样,查询到的都是两个表的交集数据(内连接的可读性更强,推荐使用内连接)

select * from A表 join B表 on 关联关系 where 其他条件;
#查询工资高于2000的员工姓名,工资和部门信息.
select e.name,sal,d.* from dept d join emp e on e.dept_id = d.id where sal > 2000;
1
2
3

外连接

• 查询一张表的全部和另外一张表的交集数据
• left就是以join左边表格为基准表格

select * from A表 left/right join B表 on 关联关系 where 其他条件;
1

如何查询多对多表中的数据

select t.name,s.name
from teacher t
    join t_s ts on t.id = ts.tid
    join student s on s.id = ts.sid
where s.name = '小明';
1
2
3
4
5