MySQL数据库基础语法day_01

MySQL数据库基础语法

–SQL语句分类

1. DDL(Data Definition Language) 数据定义语言
   用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter等

2. DML(Data Manipulation Language) 数据操作语言
   用来对数据库中表的数据进行增删改。关键字：insert, delete, update等

3. DQL(Data Query Language) 数据查询语言
   对数据库进行数据查询，关键字select

4. DCL(Data Control Language)数据控制语言(了解)

   是用来设置或更改数据库用户或角色权限的语句，这个比较少用到

学习导图

– DML操作数据库

-- 直接创建数据库db1
CREATE DATABASE db1;
-- 判断是否存在并创建数据库db2
CREATE DATABASE IF NOT EXISTS db1;
-- 创建数据库db3并指定字符集为gbk
CREATE DATABASE db3 DEFAULT CHARACTER SET gbk;
-- 查看所有的数据库
SHOW DATABASES;
-- 查看某个数据库的定义信息
SHOW CREATE DATABASE db3;
-- 将db3数据库的字符集改成utf8
ALTER DATABASE db3 DEFAULT CHARACTER SET utf8;
-- 删除db2数据库
DROP DATABASE db3;
-- 查看正在使用的数据库
SELECT DATABASE();
-- DDL操作表
-- 创建student表包含id,name,birthday字段
CREATE TABLE student(
	id INT,
	NAME VARCHAR(20),
	birthday DATE
);
-- 查看mysql数据库中的所有表
SHOW TABLES;
-- 查看student表的结构
DESC student;
-- 查看student的创建表SQL语句
SHOW CREATE TABLE student;
-- 创建s1表，s1表结构和student表结构相同
CREATE TABLE s1 LIKE student;
-- 直接删除表s1表
DROP TABLE s1;
-- 判断表是否存在并删除s1表
DROP TABLE IF EXISTS s1;
-- 为学生表添加一个新的字段remark,类型为varchar(20)
ALTER TABLE student ADD remark VALUES(20);
-- 将student表中的remark字段的改成varchar(100)
ALTER TABLE student MODIFY remark VALUES(100);
-- 将student表中的remark字段名改成intro，类型varchar(30)
ALTER TABLE student CHANGE remark intro VALUES(30);
-- 删除student表中的字段intro
ALTER TABLE student DROP intro;
-- 将学生表student改名成student2
RENAME TABLE student TO student2;
-- 将sutden2表的编码修改成gbk
ALTER TABLE student DEFAULT CHARACTER SET gbk;

– DML操作表中记录

-- 创建student表包含id,name,birthday,sex,address字段
CREATE TABLE student(
	id INT,
	NAME VARCHAR(20),
	birthday DATE,
	sex CHAR(1),
	address VARCHAR(100)
);
-- 插入数据,所有的字段名都写出来
INSERT INTO student (id,NAME,birthday,sex,address) VALUES(1,'小明','2000-02-1','男','深圳');
-- 插入数据,不写字段名
INSERT INTO student VALUES(2,'小红','2002-03-6','女','上海');
-- 插入部分数据，往学生表中添加 部分数据
INSERT INTO student (id,NAME) VALUES (3,'小样');
-- 不带条件修改数据，将所有的性别改成女
UPDATE   student SET sex = '女';
-- 带条件修改数据，将id号为1的学生性别改成男
UPDATE  student SET sex='男' WHERE id=1;
-- 一次修改多个列，把id为3的学生，生日改成1988-08-08，address改成北京
UPDATE student SET sex='女',NAME='小雅' WHERE id=3;
-- 带条件删除数据，删除id为3的记录
DELETE FROM student WHERE id=3;
-- 不带条件删除数据,删除表中的所有数据
DELETE FROM student;
-- 创建表学生表st5, 包含字段(id, name, age)将id做为主键
-- 创建表时添加主键: 字段名 字段类型 PRIMARY KEY
CREATE TABLE st5(
id INT PRIMARY KEY,
NAME VARCHAR(20),
age INT
);
-- 添加有问题的数据: 
INSERT INTO st5 (id,NAME,age) VALUES(NULL,'小红',15);
-- 删除主键
ALTER TABLE st5 DROP PRIMARY KEY;
-- 在已有表中添加主键
ALTER TABLE st5 ADD PRIMARY KEY(id);
-- 主键自动增长
-- 创建学生表st6, 包含字段(id, name, age)将id做为主键并自动增长
CREATE TABLE st6(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT
);
-- 主键默认从1开始自动增长
INSERT INTO st6 VALUES(NULL,'小小',16);
-- 创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
CREATE TABLE st7(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) UNIQUE
);
-- 创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL
CREATE TABLE st8(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)UNIQUE NOT NULL
);
-- 添加一条name为null的数据: Column 'name' cannot be null
INSERT INTO st8 VALUES(NULL,NULL);
-- 默认值
-- 创建一个学生表st9，包含字段(id,name,address)， 地址默认值是广州
CREATE TABLE st9(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) UNIQUE NOT NULL DEFAULT'小哦'
);
-- 添加一条记录,使用默认地址
INSERT INTO st9 VALUES();
DELETE FROM st9;

– DQL查询记录

-- 查询student表中所有字段
SELECT sex,id,NAME,birthday,address FROM student;
SELECT * FROM student;
-- 查询student表中的name和birthday
SELECT NAME,birthday FROM student;
-- 查询student表中name和birthday列
-- name列的别名为姓名，birthday列的别名为生日
SELECT NAME 姓名,birthday 生日 FROM student;
-- 查询address列并且结果不出现重复
SELECT DISTINCT address FROM student;
-- 添加数学，英语成绩列，给每条记录添加对应的成绩
ALTER TABLE student ADD math INT;
ALTER TABLE student ADD english INT;
ALTER TABLE student ADD math INT,ADD english INT;
-- 查询结果参数运算
-- 查询英语和数学总成绩
SELECT math + english FROM student;
-- 给总取别名
SELECT math + english 总分 FROM student;
-- 查询姓名，地址，将每个人的数学成绩+10分
SELECT NAME,address math + 10 FROM student;
-- 蠕虫复制
CREATE TABLE s1 TO student;
-- 将student表中的数据添加到student2表中
INSERT INTO student2 SELECT * FROM student;
CREATE DATABASE com117;
-- 条件查询
-- 准备数据
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
age INT,
sex VARCHAR(20),
address VARCHAR(100),
math INT,
english INT
);
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES
 (1,'马云',55,'男','杭州',66,78),
 (2,'马化腾',45,'女','深圳',98,87),
 (3,'马景涛',55,'男','香港',56,77),
 (4,'柳岩',20,'女','湖南',76,65),
 (5,'柳青',20,'男','湖南',86,NULL),
 (6,'刘德华',57,'男','香港',99,99),
 (7,'马德',22,'女','香港',99,99),
 (8,'德玛西亚',18,'男','南京',56,65);
-- 查询math分数大于80的学生
SELECT * FROM student WHERE math > 80;
-- 查询english分数小于80的学生
SELECT * FROM student WHERE english < 80;
-- 查询age不等于20岁的学生
SELECT * FROM student WHERE age != 20;
SELECT id 序号,NAME 姓名,age 年龄,sex 性别,address 地址,math 数学,english 英语 FROM student;
-- 逻辑运算符
-- 查询age大于35且性别为男的学生
SELECT * FROM student WHERE age > 35 AND sex='男';
-- 查询age大于35或性别为女的学生
SELECT * FROM student WHERE age > 35 OR sex='女';
-- 查询id是1或3或5的学生
SELECT * FROM student WHERE id=1 OR id=3 OR id=5;
-- in在...里面，只要满足()里面的数据都可以省略
-- 查询id是1或3或5的学生
SELECT * FROM student WHERE id IN(1,3,5);
-- 范围：between值1AND 值2 表示从值1到值2范围，包头又包尾
-- 查询english成绩大于等于75，且小于等于90的学生
SELECT * FROM student WHERE english >= 75 OR english <= 90;
-- 模糊查询like
-- 查询姓马的学生
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询姓名中包含德字德学生
SELECT * FROM student WHERE NAME LIKE '%德%';
-- 查询性马，且性姓名只有三个字的学生
SELECT * FROM student WHERE NAME LIKE '马__';
-- 查询姓马的或姓柳的
SELECT * FROM student WHERE NAME LIKE '马%' OR NAME LIKE'柳%';

-- 排序
-- 单列排序
-- 查询所有数据，使用年龄升序排序
SELECT * FROM student ORDER BY age ASC;
-- 查询所有数据，使用年龄降序排序
SELECT * FROM student ORDER BY age DESC;

-- 组合排序
-- 查询所有数据，在年龄降序排序的基础上，如果年龄形同再以数学成绩降序排序
SELECT * FROM student3 ORDER BY age DESC,math DESC;

-- 聚合函数
-- select 聚合函数（字段）from 表名；
-- 查询student学生总分数，count统计的时候null不算
SELECT COUNT(id) FROM student;
SELECT COUNT(english) FROM student;
-- 通常是这样使用
SELECT COUNT(*) FROM student;
-- 查询年龄大于40的总分
SELECT COUNT(*) FROM student WHERE age > 40;
-- 查询数学成绩总分
SELECT SUM(math)总分 FROM student;
-- 查询数学成绩最低分
SELECT MIN(math) FROM student;
-- 查询数学成绩平均分
SELECT AVG(math) FROM student;
-- 省略后面两个0
SELECT FORMAT(AVG(math),2) FROM student;
-- 了解：求数学成绩最大值和最小值的差
SELECT MAX(math) - MIN(math) FROM student;
-- 根据姓名，性别查询最低分
SELECT NAME,sex, MIN(math) FROM student; 

-- 分组查询
SELECT * FROM student GROUP BY id;
-- 按性别分组：注意，一般分组都会查询分组的字段
SELECT sex FROM student GROUP BY sex;

-- 查询男女各有多少
-- 先按性别分组，男，女
-- 使用聚合函数统计每组的数量
SELECT sex,COUNT(*) FROM student GROUP BY sex;
-- 查询年龄大于25岁的人，按性别分组，统计分组的人数
SELECT sex,COUNT(*) FROM student WHERE age > 25 GROUP BY sex;
-- 查询年龄大于25岁的人，按性别分组，统计分组的人数，并只显示性别人数大于2的数据
SELECT sex,COUNT(*) FROM student WHERE age > 25 GROUP BY sex HAVING COUNT(*) > 2;

-- limit
-- 准备数据
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES 
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
-- 查询学生表中数据，跳过当前2条，显示6条
SELECT * FROM student LIMIT 2,6;
-- 假设我们每一页显示5条数据的方案来分页
-- 第一页：跳过0条显示5条数据
SELECT * FROM student LIMIT 5;
-- 第二页：跳过5条显示5条数据
SELECT * FROM student LIMIT 5,5; -- 如果最后一条没有数据，那么有多少就显示多少
-- 第三页：跳过10条数据，获取5条
SELECT * FROM student LIMIT 10,5;