MySql基础语法

MySQL手册

DDL : 操作数据库、表

操作数据库 : CRUD

1. 创建(Create)

• 创建数据库

CREATE DATABASE 数据库名;

• 判断数据库是否已经存在，不存在则创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名;

• 创建数据库，并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

练习 : 直接创建数据库db1

  create database if not exists student character set utf8;

练习 : 判断数据库db1是否存在,如果不存在则创建数据库db2

  create database if not exists student character set utf8;

练习 : 创建数据库student,判断是否存在，并制定字符集为utf8

create database if not exists student character set utf8;

2. 查询(Retrieve)

• -查询所有数据库

show databases;

• 查询某个数据库的字符集 : 查询某个数据库的创建语句

show create database 数据库名称;

3. 修改(Update)

• 修改数据库的字符集

alter database 数据库名称 character set 字符集;

• 修改数据库默认的字符集

   ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;

4. 删除(Delete)

• 删除数据库

DROP DATABASE 数据库名;

• 判断数据库存在，存在再删除

drop database if exists 数据库名;

5. 使用数据库

• 查询当前正在使用的数据库

SELECT DATABASE();

• 使用数据库

USE 数据库名;

操作表 : CRUD

1. 创建(Create)

• 语法

   create table 表名(列名1 数据类型1,列名1 数据类型1,...,列名n 数据类型n);
   -- 注意：最后一列，不需要加逗号

• 常用数据类型

类型	描述
int	整型
double	浮点型
varchar	字符型
date	日期,只包含年月日，yyyy-MM-dd,
datetime	包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp	时间戳 ,如果不给这个字段赋值，或赋值为null，则默认使用当前的系统时间

• 创建表

create table student(
	id int,					-- id
	name varchar(32),		-- 姓名
	age int ,				-- 年龄
	score double(4,1),		-- 成绩
	birthday date,			-- 生日
	insert_time timestamp   -- 时间
);

• 复制表

create table 表名 like 被复制的表名;

2. 查询(Retrieve)

• 查询某个数据库中的所有表名称

SHOW TABLES;

• 查询表结构

DESC 表名;

• 查看创建表的SQL语句

SHOW CREATE TABLE 表名;

3. 修改(Update)

• 修改表名

alter table 表名 rename to 新的表名;

• 修改表的字符集

alter table 表名 character set 字符集;

• 添加一列

alter table 表名 add 列名 数据类型;

• 修改列名 类型

alter table 表名 change 列名 新列名 新数据类型;

alter table 表名 modify 列名 新数据类型;

• 删除列

alter table 表名 drop 列名;

4. 删除(Delete)

• 直接删除

DROP TABLE 表名;

• 判断表是否存在，如果存在则删除表

DROP TABLE IF EXISTS 表名;

DML：增删改表中数据

1. 添加数据

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

注意:

1. 列名和值要一 一对应。

2. 如果表名后，不定义列名，则默认给所有列添加值

   insert into 表名 values(值1,值2,...值n);

3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来

4. 没有添加数据的字段会使用NULL

2. 删除数据

DELETE FROM 表名 [WHERE 条件表达式]

注意:

1. 如果不加条件，则删除表中所有记录

2. 如果要删除所有记录

   delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作

   TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表

truncate和delete的区别：truncate相当于删除表的结构，再创建一张表。

• 练习

-- 带条件删除数据，删除id为1的记录
delete from student where id=1;

-- 不带条件删除数据,删除表中的所有数据
delete from student;

3. 修改数据

update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

注意:

1. 如果不加任何条件，则会将表中所有记录全部修改

• 练习

-- 不带条件修改数据，将所有的性别改成女
update student set sex = '女';

-- 带条件修改数据，将id号为2的学生性别改成男
update student set sex='男' where id=2;

-- 一次修改多个列，把id为3的学生，年龄改成26岁，address改成北京
update student set age=26, address='北京' where id=3;

DQL：查询表中的记录

1. 语法

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组之后的条件
order by
	排序
limit
	分页限定

2.基础查询

• 多字段查询

select 字段名1，字段名2... from 表名；

select * from 表名; -- 查询所有字段，则可以使用*来替代字段列表,但工作不建议使用

• 去除重复的结果

select distinct 字段名 from 表名;

• 计算列

select 字段名1,字段名2,字段名3, 字段名2 + 字段名3 from 表名;

注意:

如果有null ,则计算结果为null

select 字段名1,字段名2,字段名3, 字段名2 + ifnull(字段名3,0) from 表名; -- 如果字段名3为null,则替换为0

• 起别名

select 字段名1,字段名2,字段名3, 字段名2 + ifnull(字段名3,0) as 新名字 from 表名; -- as可以用空格代替

select 字段名1 [新名字],字段名2 [新名字],字段名3 [新名字], 字段名2 + ifnull(字段名3,0) as 新名字 from 表名;

3. 条件查询 WHERE

• 逻辑运算符

逻辑运算符	说明
and 或 &&	与,SQL中建议使用前者,后者并不通用
or 或 ||	或
not 或 !	非

SELECT * FROM student WHERE age > 20; -- 查询年龄大于20岁

SELECT * FROM student WHERE age =20;  -- 查询年龄等于20岁

• 查询年龄不等于20岁

SELECT * FROM student WHERE age !=20; 
SELECT * FROM student WHERE age <> 20;

• 查询年龄大于等于20 小于等于30

SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

• 查询年龄22岁，18岁，25岁的信息

SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN (22,18,25);

• 查询英语成绩为null

SELECT * FROM student WHERE english IS NULL;

• 查询英语成绩不为null

SELECT * FROM student WHERE english  IS NOT NULL;

4. 模糊查询 LIKE

% ：匹配多个任意字符

_ : 匹配单个任意字符

-- 查询姓马的有哪些？
SELECT * FROM student WHERE NAME LIKE '马%'; 

-- 查询姓名第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";

-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';

-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';

5. 排序查询 ORDER BY

ASC : 升序

DESC : 降序

SELECT * FROM 表名 ORDER BY 字段名 ASC;

SELECT * FROM student ORDER BY math DESC;

• 组合排序,如果第1个字段相等，则按第2个字段排序，依次类推

SELECT * FROM student ORDER BY math DESC,english ASC;

6. 聚合函数

使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值NULL。

• 五个聚合函数

聚合函数	作用
max(列名)	求这一列中的最大值
min(列名)	求这一列中的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列求总和

• 查询学生总数

SELECT COUNT(id) AS 总人数 FROM student;

SELECT COUNT(*) AS 总人数 FROM student;

7. 分组查询 GROUP BY

GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

分组查询是指使用 GROUP BY语句对查询信息进行分组，相同数据作为一组

SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

• 按性别将学生分成2组

-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;

• 查询男女各多少人

  • 查询所有数据,按性别分组。

  • 统计每组人数

SELECT sex,COUNT(id) FROM student GROUP BY sex;

• 查询年龄大于25岁的人,按性别分组,统计每组的人数

  • 先过滤掉年龄小于25岁的人。

  • 再分组。

  • 最后统计每组的人数

SELECT sex,COUNT(id) FROM student WHERE age > 25 GROUP BY sex;

• 查询年龄大于25岁的人，按性别分组，统计每组的人数，并只显示性别人数大于2的数据

SELECT sex, COUNT(id) FROM student WHERE age>25 GROUP BY sex HAVING COUNT(id)>2;

• having与where的区别

子名	作用
where 子句	1. 对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。 2. where后面不可以使用聚合函数
having子句	1. having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。 2. having后面可以使用聚合函数

8.分页查询 limit

LIMIT是限制的意思，所以LIMIT的作用就是限制查询记录的条数。

LIMIT语法格式：

LIMIT offset,length;

offset：起始行数，从0开始计数，如果省略，默认就是0

length： 返回的行数

-- 查询学生表中数据，从第3条开始显示，显示6条。
select * from student limit 2,6;

-- 如果第一个参数是0可以省略写：
select * from student limit 5;

-- 最后如果不够5条，有多少显示多少
select * from student limit 10,5;

约束

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

1. 非空约束 not null

2. 主键约束 primary key

1. 主键的作用 用来唯一标识数据库中的每一条记录

2. 哪个字段应该作为表的主键？

   通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

3. 主键的特点：

   • 非空 not null

   • 唯一

3. 唯一约束 nuique

4. 外键约束 foreign key