一篇学会MySQL

SQL语句

DDL语句(数据定义语言)

数据库模式定义语言并非程序设计语言，DDL数据库模式定义语言是SQL语言（结构化查询语言）的组成部分.

SQL语言包括四种主要程序设计语言类别的语句：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL,本文是从DDL语句开始.

数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言.这些定义包括结构定义、操作方法定义等.

DDL数据库操作语句

注意SQL语句中不区分大小写.

#查询所有数据库
SHOW DATABASES;
#查询当前数据库
SELECT DATABAASE();
#创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名称 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
#删除数据库
DROP DATABASE [IF EXISTS] 数据库名称;
#使用某数据库
USE 数据库名;
#查询当前数据库所有表
SHOW TABLES;
#查询表结构
DESC 表名称;
#查询指定表的建表语句
SHOW CREATE TABLE 表名;

DDL-表操作语句

创建表语句

#创建一个新的表以及表中字段,字段类型
CREATE TABLE 表名(
	字段1 字段1类型[COMMENT 字段1注释],
	字段2 字段2类型[COMMENT 字段2注释],
    字段3 字段3类型[COMMENT 字段3注释],
    字段4 字段4类型[COMMENT 字段4注释]
)

常用数值类型:

常见的日期类型:

常见的字符串类型:

创建testTable表

添加name字段字段类型为char,无论存放什么数据都占用10个字符,效率较高.

CHAR与VARCHAR相似,VARCHAR只占用使用的字符空间,效率较低.

添加age字段字段类型为TINYINT ,存储空间中存放无符号数,允许存放空数据

添加score字段,字段类型为DOUBLE,存放4位的空间,保留1位小数

create table testTableb(
stuname char(10) not null comment ‘名称’,
age tinyint UNSIGNED null comment ‘年龄’,
score double(4,1) comment ‘分数’
)

表结构的修改语句

#为表中新添加字段
ALTER TABLE 表名称 字段名 类型(长度) [COMMENT 注释] [约束]; 

#为testTABLE表中新添加字段

alter table testtable add field varchar(1) comment ‘新添加测试字段’

#修改表中数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
#修改表中的字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

#修改表中数据类型
alter table testTable modify field varchar(10);
#修改表中的字段名
alter table testTable change field new_field varchar(20) comment ‘更改后的属性’;

#修改表名称
ALTER TABLE 表名 RENAME TO 新表名;

alter table testTable rename TO new_testTable;

表结构的删除语句

#将某个表中的字段删除
ALTER TABLE 表名 DROP 字段名;

alter table new_testTable dropnew_field ;

#删除某个表
DROP TABLE [IF EXISTS] 表名;
#删除指定表,并重新创建该表
TRUNCATE TABLE 表名;

DML(数据操作语言)

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作.DML中最核心的是三种语句,即添加数据,修改数据,删除数据的SQL语句.

添加表数据

#给指定字段添加数据
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,值...);

insert into new_testtable (stuname,age,score,new_field) values (“wjk”,20,99.9,“a”);

insert into new_testtable values (“wjk_a”,18,60,“b”);

insert into new_testtable (stuname,age,score,new_field) values (“wjk_b”,20,99.9,“a”),(“wjk_c”,20,99.9,“a”);

insert into new_testtable values (“wjk_a”,18,60,“b”),(“wjk_a”,18,60,“b”),(“wjk_a”,18,60,“b”);

修改表数据

#修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据.
UPDATE 表名 SET 字段名1= 值1 , 字段名2 = 值2....[WHERE 条件];

update new_testtable set stuname = “updateData”;

删除表数据

#DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据.
#DELETE语句不能删除某一个字段的值(可以使用 UPDATE).
DELETE FROM 表名 [WHERE 条件]

delete from new_testtable ;

DQL(数据查询语言)

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

查询表数据

基本查询语句

#查询多个字段
SELECT 字段1, 字段2,字段3... FROM 表名;
SELECT * FROM 表名;
#设置别名
SELECT 字段1 [AS 别名1],字段2 [AS别名2] .. FROM 表名;
#去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

select * from new_testtable ;

select age as ‘年龄’,score as ‘分数’ FROM new_testtable ;

select distinct stuname from new_testtable ;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

select * from new_testtable where age>18;

select * from new_testtable where score is null;

select * from new_testtable where score is not null;

select * from new_testtable where stuname like ‘%wjk%’;

select * from new_testtable where stuname like ‘wjk%’;

select * from new_testtable where stuname like ‘%wjk’;

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

select * from new_testtable where age between 20 and 60;

等价于:

select * from new_testtable where age >=20 and age<=40;

select * from new_testtable where age != 20;

聚合函数

聚合函数是将一列数据作为一个整体,进行纵向计算,NULL值不进入聚合函数计算.下图为常用的聚合函数:

select count(*) from new_testtable;

select count(age) from new_testtable;

select avg(age) from new_testtable;

select min(age) from new_testtable;

select sum(score) from new_testtable where age=18;

分组查询

SELECT 字段列表 FROM 表名 [WHERE条件] GROUP BY 分组字段名 [ HAVING分组后过滤条件];

where. 与having区别:
执行时机不同: where是分组之前进行过滤，不满足where条件,不参与分组;而having是分组之 后对结果进行过滤.
判断条件不同: where不能对聚合函数进行判断，而having可以.

分组查询返回的字段一般是聚合函数和分组的字段,其他的字段没有意义.

select age,count(*) from new_testtable group by age;

select age,avg(score) from new_testtable group by age;

select age,avg(score) from new_testtable group by age having avg(score) > 60;

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 ,字段2 排序方式2;

排序方式:

• ASC:升序(默认值)
• DESC:降序

select * from new_testtable where age is not null order by age asc,stuname desc ;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

分页查阅的注意事项:

• 起始索引从0开始，起始索引= (查询页码-1) *每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

查询new_testtable表中,第1-5条数据

select * from new_testtable limit 0,5;

查询new_testtable表中,第6-10条数据

select * from new_testtable limit 5,5;

查询emp表中 性别为男,同时年龄在20-40之间的前五个信息,查询的结果按照年龄升序

select * from emp where gender ‘男’ and age between 20 and 40 order by age asc Limit 5 ;

到此我们的DQL语句也学习完毕,这个时候我们还需要了解不同的DQL之间的执行顺序.一般的DQL语句书写格式如下

# FROM->WHERE->GROUP BY->SELECT->ORDER BY->LIMIT
SELECT
	字段列表 
FROM
	表名列表 
WHERE
	条件列表 
GROUP BY
	分组字段列表 
HAVING
	分组后条 列表 
ORDER BY
	排序字段列表 
LIMIT 
	分页参数

DCL(数据控制语言)

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

#查询用户
USE mysql;
SELECT * FROM user;
#创建用户
CREATE USER '用户名’@'主机名’ IDENTIFIED BY '密码';
#修改用户密码
SET PASSWORD FOR '用户名’@'主机名’ = PASSWORD('新密码’);
#删除用户
DROP USER '用户名'@'主机名’;

create user ‘wjk’@‘localhost’ identified by ‘123456’;

create user ‘wjk’@‘%’ identified by ‘123456’;

set password for ‘wjk’@‘localhost’ = password (‘1234567’);

drop user ‘wjk’@‘localhost’;

DCL-权限管理

#查询权限
SHOW GRANTS FOR‘用户名'@'主机名’ ;
#授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
#撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名'@'主机名';

show grants for ‘wjk’@‘localhost’;

grant all on testDatabase.* to ‘wjk’@‘localhost’;

grant all on . to ‘wjk’@‘localhost’;

MySQL常用函数

函数是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

MySQL中内置了很多的字符串函数,常用的几个如下:

select connat(‘hello’,‘world!’);

select lower(‘HELLO’);

select upper(‘hello’);

select lpad(‘str’,5,‘_’);

select rpad(‘str’,5,‘_’);

select trim(’ hello world! ');//不去除中间的空格

select substring(‘hello’,1,5)//print hello

update new_testtable set stuname= lpad(stuname,5,‘*’);

update new_testtable set age = lpad(age,3,‘9’)

数值函数

常见的数值函数:

select ceil(1.1);

select floor(1.1);

select mod(5,4);

select rand();

select round(3.444,4);

select lpad(round(rand()*100000,0),6,‘0’);

日期函数

常见的日期函数:

select curdate();

select curtime();

select now();

select year(now());

select month(now());

select day(now());

select date_add(now(),interval 70 year);

select date_add(now(),interval 70 day);

select datediff(now(),date_add(now(),interval 70 day));

流程函数

流程函数可以在SQL语句中实现条件筛选,提高语句的效率.

select if(true,‘ok’,‘false’);

select ifnull(‘’,‘false’);

select ifnull(null,‘false’);

查询new_testtable表中,用户等级

select (case stuname when ‘wjk_a’ then ‘第一个root用户’ when ‘wjk_b’ then ‘第二个root用户’ else ‘其他用户’ end) from new_testtable;

新建score表,查询成绩等级,80以上A,60以上B,其他分数C

CREATE TABLE score (
id INT COMMENT ‘ID’,
NAME VARCHAR (20) COMMENT ‘姓名’,
math INT COMMENT ’ 数学’,
english INT COMMENT ‘英语’,
chinese INT COMMENT ‘语文’
) COMMENT ‘学员成绩表’;
INSERT INTO score (id,NAME,math,english,chinese) VALUES

​ (1,‘Tom’,67, 88, 95),

​ (2,‘Rose’,23,66,90),

​ (3,‘Jack’,56,98,76);

SELECT
( CASE WHEN math > 80 THEN ‘A’ WHEN math > 60 AND math < 80 THEN ‘B’ ELSE ‘C’ END ) AS ‘成绩’ ,
( CASE WHEN english > 80 THEN ‘A’ WHEN english > 60 AND english < 80 THEN ‘B’ ELSE ‘C’ END ) AS ‘成绩’ ,
( CASE WHEN chinese > 80 THEN ‘A’ WHEN chinese > 60 AND chinese < 80 THEN ‘B’ ELSE ‘C’ END ) AS ‘成绩’
FROM score;

MySQL约束

约束

约束是作用于表中字段上的规则,用于限制存储在表中的数据.为了保证数据库中数据的正确、有效性和完整性.

约束是作用于表中字段.上的，可以在创建表/修改表的时候添加约束.

创建一个如下图所示约束创建表结构:

CREATE TABLE USER (
id INT PRIMARY KEY auto_increment COMMENT ‘主键’,
NAME VARCHAR ( 10 ) NOT NULL UNIQUE COMMENT ‘姓名’,
age INT CHECK (age>0 && age<=120) ,
STATUS CHAR ( 1 ) DEFAULT ‘1’ COMMENT ‘状态’,
gender CHAR ( 1 ) COMMENT ‘性别’
) comment ‘用户表’;

测试约束:

insert into user (name ,age, status, gender) values(‘Toml’ ,19,‘1’,‘男’),(‘Tom2’ ,25,‘0’,男’);
insert into user (name , age, status, gender) values (‘Tom3’ ,19,‘1’，男’);
insert into user (name , age, status, gender) values (null,19,‘1’,‘男’);
insert into user (name , age, status, gender) values (‘Tom3’ ,19,‘1’,男’);
insert into user (name , age, status, gender) values (‘Tom4’ ,80,‘1’，‘男’);
insert into user (name , age, status, gender) values (‘Tom5’ .-1,‘1’,‘男’);
insert into user (name , age, status, gender) values (‘Tom5’ ,121,‘1’,‘男’);
insert into user(name , age , gender) values ('Tom5 ',120,‘男’);

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性.

添加外键
CREATE TABLE表名(
	字段名数据类型,
	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);

外键的关联

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
ALTER TABLE 表名 DROP FOREIGN KEY外键名称;

外键的删除/更新

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) 
ON UPDATE CASCADE 
ON DELETE CASCADE;

CREATE TABLE dept (
id INT auto_increment COMMENT ‘ID’ PRIMARY KEY,
NAME VARCHAR ( 50 ) NOT NULL COMMENT ’ 部门名称’ ) COMMENT ’ 部门表’;
INSERT INTO dept (NAME)
VALUES
(‘研发部’),
(‘市场部’),
(‘财务部’),
(‘销售部’)，
(‘总经办’)

CREATE TABLE emp(
id INT auto_increment COMMENT ‘ID’ PRIMARY KEY,
NAME VARCHAR ( 50 ) NOT NULL COMMENT ‘姓名’,
age INT COMMENT ‘年龄’,
job VARCHAR (20) COMMENT ‘职位’,
salary INT COMMENT ’ 薪资’,
entrydate date COMMENT ’ 入职时间’,
managerid INT COMMENT ‘直属领导ID’,
dept_id INT COMMENT ‘部门ID’
)COMMENT ‘员工表’;

INSERT INTO emp (NAME,age,job,salary,entrydate,managerid,dept_id )
VALUES
( ‘金庸’,66,‘总裁’, 20000,‘2000-01-01’,NULL, 5 ),
(‘张无忌’, 20,‘项目经理’,12500,'2005-12-05 ', 1, 1 ),
(‘杨道’, 33, ‘开发’, 8400, ‘2000-11-03’, 2, 1 ),
(‘韦一笑’, 48, ‘开发’, 11000, ‘2002-02-05’, 2, 1 ),
(‘常遇春’,43, ‘开发’,10500, ‘2004-09-07’, 3, 1 ),
(‘小昭’, 19,‘程序员鼓励师’, 6600, '2004-10-12 ',2,1);

//插入外键,添加后外键关联字段不能随意修改

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

//删除外键

alter table emp drop foreign key fk_emp_dept_id ;

//外键更新/删除时关联表受到影响

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

多表查询

项目开发中，在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构，由于业务之间相互关联,所以各个表结构之间也存在着各种联系.

基本上分为三种: 一对多(多对一)、多对多、一对一

一对多(多对一):

• 案例: 部门与员工的关系
• 关系:一个部门对应多个员工， 一个员工对应一个部门
• 实现: 在多的一方建立外键，指向一的一方的主键

多对多:

• 案例:学生与课程的关系
• 关系:一个学生可以选修多门课程，一门课程也可以供多个学生选择
• 实现:建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

create table student(
id int auto_increment primary key comment ‘主键ID’,
name varchar(10) comment ‘姓名’,
no varchar(10) comment ‘学号’
)comment ‘学生表’;
insert into student values (NULL,‘黛绮丝’,‘2000100101’), (NULL,‘谢逊’,‘2000100102’),(NULL,‘殷天正’,‘2000100103’);

CREATE TABLE course ( id INT auto_increment PRIMARY KEY COMMENT ‘主键ID’, NAME VARCHAR ( 10 ) COMMENT ‘课程名称’ ) COMMENT ‘课程表’;
INSERT INTO course
VALUES
( NULL, ‘Java’ ),
( NULL, ‘PHP’ ),
( NULL, ‘MySQL’ ),
( NULL, ‘Hadoop’ );

CREATE TABLE student_course (
id INT auto_increment COMMENT ‘主键’ PRIMARY KEY,
studentid INT NOT NULL COMMENT ‘学生ID’,
courseid INT NOT NULL COMMENT ‘课程ID’,
CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES course ( id ),
CONSTRAINT fk_studentid FOREIGN KEY (studentid ) REFERENCES student ( id )
) COMMENT ‘学生课程中间表’;
INSERT INTO student_course
VALUES
( NULL, 1, 1 ),
( NULL, 1, 2 ),
( NULL, 1, 3 ),
( NULL, 2, 2 ),
( NULL, 2, 3 ),
( NULL, 3, 4 );

一对一:

• 案例:用户与用户详情的关系
• 关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一 张表中，其他详情字段放在另一张表中，以提升操作效率
• 实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)

CREATE TABLE tb_USER (
id INT auto_increment PRIMARY KEY COMMENT ‘主键ID’,
NAME VARCHAR ( 10 ) COMMENT ‘姓名’,
age INT COMMENT ‘年龄’,
gender CHAR ( 1 ) COMMENT “1:男，2:女”,
phone CHAR ( 11 ) COMMENT ‘手机号’
) COMMENT ‘用户基本信息表’;
CREATE TABLE tb_user_edu (
id INT auto_increment PRIMARY KEY COMMENT ‘主键ID’,
degrele VARCHAR ( 20 ) COMMENT ‘学历’,
major VARCHAR ( 50 ) COMMENT ’ 专业 ',
primaryschool VARCHAR ( 50 ) COMMENT ‘小学’,
middleschool VARCHAR ( 50 ) COMMENT ‘中学’,
university VARCHAR ( 50 ) COMMENT ‘大学’,
userid INT UNIQUE COMMENT ‘用户ID’,
CONSTRAINT fk_userid FOREIGN KEY ( userid ) REFERENCES tb_user ( id )
) COMMENT ‘用户教育信息表’;

;
INSERT INTO tb_USER ( id, NAME, age, gender, phone )
VALUES
( NULL,‘黄渤’, 45, ‘1’, ‘188800111’ ),
( NULL,‘冰冰’, 35, ‘2’, ‘1800002222’ ),
( NULL,‘码云’, 55, ‘1’, ‘1880008888’ ),
( NULL,‘李彦宏’,50,‘1’,‘188000999’ );
);
INSERT INTO tb_user_edu (degrele,major,primaryschool,middLeschool,university,userid)
VALUES
( ‘本科’, ‘舞蹈’, ‘静安区第一小学’, ‘静安区第一中学’, ‘北京舞蹈学院’, 1 ),
( ‘硕士’, ‘表演’, ‘朝阳区第一小学’, ‘朝阳区第一中学’, ‘北京电影学院’, 2 ),
( ‘本科’, ‘英语’, ‘杭州市第一小学’, ‘杭州市第一中学’, ‘杭州师范大学’, 3 ),
( ‘本科’, ‘应用数学’, ‘阳泉第一小学’, ‘阳泉区第一中学’, ‘清华大学’, 4 );