[MySQL] 语法笔记

注释：

#单行注释

-- 单行注释

/*
多行注释
*/

 

一、DDL(Data Definition Language)数据定义语言

-- 1 查看所有数据库 
show databases;

-- 2 创建数据库 
create database db1;
create database db2 character set gbk;

-- 3 更改数据库 
alter database db1 character set gbk;

-- 4 删除数据库 
drop database db2;

-- 5 使用数据库 
use db1;

-- 6 查看当前数据库
select database();

-- 7 在当前数据库中创建表 
create table tb1 (
    id mediumint,
    name varchar(10),
    age tinyint,
    sex varchar(10)
);

-- 8 查看当前数据库中的表 
show tables;

-- 9 查看表中的字段 
desc tb1;

-- 10 向表中添加字段（列）
alter table tb1 add salary int;

-- 11 修改表中的字段长度 
alter table tb1 modify name varchar(15);

-- 12 删除表中的字段 
alter table tb1 drop salary;

-- 13 修改表名
rename table tb1 to tb2;

-- 14 查看表的创建细节
show create table tb2;

-- 15 修改表的字符集 
alter table tb2 character set gbk;

-- 16 修改表中的字段名 
alter table tb2 change age salary float(8,2);

 

二、DML(Data Management Language)数据操作语言

/* 修改数据库模式
SQL_SAFE_UPDATES = 1时，不带where和limit条件的update和delete操作语句是无法执行的，即使是有where和limit条件但不带key column的update和delete也不能执行。
QL_SAFE_UPDATES = 0时，update和delete操作将会顺利执行。
set sql_safe_updates = 0;
*/

-- 1 查询表中的数据 
select * from tb2;

-- 2 向表中插入数据
insert into tb2(id, name, salary, sex) value(1, '张三', 9999.99, 'male');
insert into tb2 value(2,'李四',250,'male');
insert into tb2(id, name, salary, sex) value(3, '刘亦菲', null, 'female');
insert into tb2 value(4, '刘德华', 88888.88, 'male');
insert into tb2(id, name, salary, sex) value(5, '林志玲', 6666, 'female');

-- 3 修改表中的数据 
update tb2 set name = '王五' where name = '李四';

-- 4 删除表中数据
delete from tb2 where id = 2;
delete from tb2;

 

三、DQL(Data Query Language)数据查询语言

-- 1 查询所有列
select * from tb2;
-- 2 查询指定列
select id,name from tb2;

/* 3 条件查询
条件查询就是在查询时给出WHERE子句，在WHERE子句中使用如下运算符及关键字。
    =、!=、<>、<、<=、>、>=;
    BETWEEN...AND;
    IN(set);
    IS NULL、IS NOT NULL;
    AND;
    OR;
    NOT;
*/

-- 3.1 查询name为'张三'且id为1的列
select * from tb2 where name = '张三' and id = 1;

-- 3.2 查询name为'张三'或id为2的列
select * from tb2 where name = '张三' or id = 2;

-- 3.3 查询id为1或2的列
select * from tb2 where id in(1,2);
select * from tb2 where id = 1 or id = 2;

-- 3.4 查询id不为1和2的列
select * from tb2 where id not in(1,2);
select * from tb2 where not(id = 1 or id = 2);

-- 3.5 查询id不为1的列
select * from tb2 where id != 1;
select * from tb2 where id <> 1;
select * from tb2 where not id = 1;

-- 3.6 查询salary不为null的列
select * from tb2 where salary is not null;
select * from tb2 where not(salary is null);

/* 4.模糊查询
模糊查询需要使用关键字LIKE。
通配符：
    _：任意一个字符
    %：任意0-n个字符
*/

-- 4.1 查询name为2个字符的列
select * from tb2 where name like '__';

-- 4.2 查询name为2个字符且第2位为'三'的列
select * from tb2 where name like '_三'

-- 4.3 查询name第一位为'刘'的列
select * from tb2 where name like '刘%'

-- 4.4 查询name中有'德'的列
select * from tb2 where name like '%德%'

 

四、DCL(Data Control Language)数据控制语言

-- 1 去除重复记录
select distinct salary from tb2;

-- 2 查看id与salary的和
-- 任何值 + NULL = NULL
select id, salary, id + salary from tb2;

-- 3 替换NULL为0
-- IFNULL(salary,0)
select id, salary, id + ifnull(salary,0) from tb2;

-- 4 给列起别名
select id as 编号, salary as 薪水, id +ifnull(salary, 0)as 和 from tb2;

-- 5 排序查询
-- 5.1 升序
select * from tb2 order by salary asc;

-- 5.2 降序
select * from tb2 order by salary desc;

-- 5.3 如果salary相同，就按id升序排序
select * from tb2 order by salary asc, id asc;

/* 6 聚合函数 count、max、min、sum、avg
聚合函数是用来做纵向运算的函数：
    COUNT()：统计指定列不为NULL的记录行数；
    MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
    MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
    SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
    AVG()：计算指定列的平均值，如果指定列类型不是数值类型，呢么计算结果为0。
*/

-- 6.1 COUNT
-- 6.1.1查询表中的记录数
select count(*) as 总行数 from tb2;

-- 6.1.2查询表中有salary的人数（NULL不计算）
select count(salary) from tb2;

-- 6.1.3查询表中salary大于2500的人数
select count(*) from tb2 where salary > 2500;

-- 6.1.4查询表中id加salary大于2500的人数
select count(*) from tb2 where id + ifnull(salary, 0) > 2500;

-- 6.2 MAX、MIN
-- 6.2.1 查询最小id和最大salary
select min(id), max(salary) from tb2;

-- 6.3 SUM、AVG
-- 6.3.1 查询所有记录的salary的和
select sum(salary) as 全部薪水 from tb2;

-- 6.3.2 查询所有记录的id和、salary和
select sum(id), sum(salary) from tb2;

-- 6.3.3 查询所有记录的id和salary的和
select sum(id + ifnull(salary, 0)) from tb2;

-- 6.3.4 统计所有记录的salary的平均值
select avg(salary) as 平均数 from tb2

-- 7 分组查询
-- 7.1 查询记录里sex分别为male和female的个数
select sex, count(*) from tb2 group by sex;

-- 7.2 查询male和female分别的salary和
select sex, sum(salary) from tb2 group by sex;

-- 7.3 分别查询male和female中salary大于2500的个数
select sex, count(*) from tb2 where salary > 2500 group by sex;

/* 8 HAVING子句
    HAVING是在分组后对数据进行过滤；
    WHERE是在分组前对数据进行过滤。
    HAVING后面可以使用聚合函数（统计函数）；
    WHERE后面不可以使用聚合函数。
*/
-- 8.1 查询salary和大于9000的sex和salary和
select sex, sum(salary) from tb2 group by sex having sum(salary) > 9000;

-- 9 LIMIT
-- 9.1 限定查询
-- 查询1到3行（0为初始行；就是从第1行开始查，查3行）
select * from tb2 limit 0, 3;

-- 9.2 分页查询
-- int currentPage = 1;		当前页
-- int pageSize = 3;	每页大小
-- select * from tb2 limit (currentPage - 1) * pageSize, pageSize;
select * from tb2 limit 0, 3;

 

五、数据完整性

/* 1 实体完整性
    实体：即表中的一行（一条记录）代表一个实体（entity）
    实体完整性的作用：表示每一行数据不重复
    约束类型：主键约束（primary key）、唯一约束（unique）、自动增长列（auto_increment）
*/

-- 1.1 主键约束（primary key 数据唯一，且不为NULL）
create table tb1{
    id int primary key,
    name varchar(20)
}

create table tb3{
	id int,
	name varchar(20),
	primary key(id)
}

alter table tb2 add constraint pk_tb2_id primary key(id);

-- 1.2 联合主键（primary key()）
create table tb4{
    id int,
    name varchar(20),
    primary key(id, name)
}

-- 1.3 唯一键约束（unique）
create table tb5{
    id int primary key,
    name varchar(20) unique
}

alter table tb2 add constraint uq_tb2_name unique(name);

-- 1.4 自动增长列（auto_increment）
create table tb6{
    id int primary key auto_increment,
    name varchar(20)
}

/* 2 域完整性
    域完整性的作用：限制单元格的数据正确，不对照此列的其他单元格
    域代表当前单元格
    域完整性约束：非空约束（not null）、默认值约束（default）
*/
-- 2.1 非空约束（not null）
create table tb7{
    id int primary key auto_increment,
    name varchar(20) not null
}

-- 2.2 默认值约束（default）
create table tb7{
    id int primary key auto_increment,
    name varchar(20) default 'Nobody'
}

-- 3 引用完整性（参照完整性 foreign key）
create table student{
    stuid int primary key,
    name varchar(20)
}

create table score{
    stuid int,
    coursename varchar(10),
    score int,
    constraint fk_student_score foreign key(stuid) references student(stuid)
}

create table score2{
    stuid int,
    coursename varchar(10),
    score int,
}
alter table score2 add constraint fk_student_score2 foreign key(stuid) references student(stuid)