MySQL基础
1. SQL概述
1.1 SQL定义
Structured Query Language 结构化查询语言,所有关系型数据库都支持的一种语言。
1.2 SQL语句分类
-
Data Definition Language (DDL数据定义语言,包括:建库,建表等)
-
Data Manipulation Language(DML数据操纵语言,包括:增删改)
-
Data Query Language(DQL数据查询语言:包括:查询)
-
Data Control Language(DCL数据控制语言,包括:创建用户,给用户权限)
1.3 SQL语法
-
结尾:所有SQL语句以分号结尾.
-
不区分大小写,无论是关键字还是数据,默认都是不区分大小写。
-
注释
注释的语法 | 说明 |
---|---|
–空格 | 单行注释 |
/* */ | 多行注释 |
# | 单行注释,只在MySql使用 |
###2. DDL(Data Definition Language)
- 数据库的增删改查
-- 创建
create database 库名;
create database if not exists 库名;
create database 库名 character set 字符集;
-- 查看所有的数据库
show databases;
-- 查看某个数据库的定义信息
show create database db1;
-- 修改
alter database 库名 character set 字符集
-- 删除
drop database 库名;
练习
-- test01 创建数据库并设置utf8编码,完成数据库的添加,删除,查询,使用。
CREATE DATABASE db CHARACTER SET utf8;
-- 查询数据库
SHOW DATABASES;
-- 使用数据库
USE db;
-- 删除数据库
DROP DATABASE db;
- MySQL的数据类型
分类 | 类型名称 | 类型说明 |
---|---|---|
整数 | tinyInt | 微整型:很小的整数(占8位二进制) 1个字节 |
smallint | 小整型:小的整数(占16位二进制) 2个字节 | |
mediumint | 中整型:中等长度的整数(占24位二进制) 3个字节 | |
int(integer) | 整型:整数类型(占32位二进制) 4个字节 | |
bigint | 大整型:占64位二进制 | |
小数 | float | 单精度浮点数,占4个字节 |
double | 双精度浮点数,占8个字节 | |
decimal(m,n) | 数值类型,m表示数值的长度,n表示小数的位数 既可以表示整数,也可以表示小数。如:decimal(10) decimal(10,2) | |
日期 | time | 只表示时间类型 |
date | 只表示日期类型 | |
datetime | 表示日期和时间类型 时间范围为:‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ | |
timestamp | 表示日期和时间类型(时间戳) 时间范围为:‘1970-01-01 00:00:01’ 到 ‘2038-01-19 03:14:07’ | |
字符串 | char(m) 定长 | 固定长度的字符串,无论使用几个字符都占满全部,M为0~255之间的整数 |
varchar(m) | 可变长度的字符串,使用几个字符就占用几个,M为0~65535之间的整数 |
- 表的增删改查
-- 表的创建
create table 表名(
字段名1 字段类型1 约束1,
字段名2 字段类型2 约束2
)
-- 查看所有表
show tables;
-- 查看表结构
desc 表名
-- 查看创建表的SQL语句
show create table 表名
-- 创建一个相同表结构的表
create table 新表 like 旧表
-- 删除表
drop table 表名
-- 修改表结构
-- 添加表列ADD
alter table 表名 add 列名 类型;
-- 修改列类型 MODIFY
alter table 表名 modify 列名 类型;
-- 修改列名 CHANGE
alter table 表名 change 旧列名 新列名 类型;
-- 删除列 DROP
alter table 表名 drop 列名;
练习
-- test02 创建数据库webdb_1,在该数据库下创建表: category,表结构查询,修改表结构,删除表.
CREATE DATABASE webdb_1;
USE webdb_1;s
-- 创建表
CREATE TABLE category (
cid INT PRIMARY KEY, -- 分类id
cname VARCHAR(100) -- 分类名称
);
-- 查看表
SHOW TABLES;
DESC category;
-- 删除表
DROP TABLE category;
-- 修改表
ALTER TABLE category ADD `desc` VARCHAR(100);
ALTER TABLE category MODIFY `desc` VARCHAR(120);
ALTER TABLE category CHANGE `desc` newdesc VARCHAR(400);
ALTER TABLE category DROP newdesc;
-- 修改表名
RENAME TABLE category TO newcategory;
-- 修改字符集
ALTER TABLE category CHARACTER SET gbk;
###3. DML (Data Manipulation Language)
- 表中数据的增删改
-- 插入全部字段
insert into 表名 values (全部列的值);
-- 插入部分字段
insert into 表名 (列名) values (值);
-- 插入多条
insert into student values (全部列值), (全部列值), (全部列值);
-- 修改所有行的记录
update 表名 set 列名=值;
-- 带条件的修改
update 表名 set 列名=值 where 条件;
-- 删除全部记录
delete from 表名;
-- 带条件的删除
delete from 表名 where 条件;
-- 另一种删除(先删除表结构,再创建一个相同的表结构)
truncate 表名;
-- 删除整个表(包括表结构)
drop table 表名;
练习
-- test08 DML之insert update delete表记录操作使用 完成表记录的添加,修改,删除操作.
-- 插入
INSERT INTO category VALUES(1003013, '情感励志');
INSERT INTO category(cid) VALUES(1020);
-- 删除
DELETE FROM category WHERE cid = 1020;
-- 修改
UPDATE category SET cid = 1 WHERE cname = '情感励志'
###4. DQL(Data Definition Language)
基本查询
select 列名 from 表名 where 条件
-- 基本查询
-- 查询所有的列
select * from 表名;
-- 查询指定列
select 列名1, 列名2 from 表名;
-- 指定别名 AS(AS可以省略)
select 列名 as 别名 from 表名
select 列名 别名 from 表名
-- 去除重复值
select distinct 列名 from 表名
-- 列运算(必须是数值类型)
select 列名1+列名2 from 表名;
条件查询
select 列名 from 表名 where 条件
-- 比较运算 <、>、=、!=、<>、
-- 逻辑运算 AND、OR、NOT
-- 范围查询
select * from 表名 where 列名 between 小值 and 大值;
-- in关键字
select * from 表名 where 列名 in (多个值);
-- like模糊查询
-- ‘%‘表示多个
-- ’_‘表示一个
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
-- 查询null值
SELECT * FROM 表名 WHERE 字段名 IS NULL;
练习
-- test09 DQL查询语句使用,创建一张新表product 录入初始化数据,完成商品的查询以及条件查询.
-- 创建商品表并准备数据:
CREATE TABLE product(
pid INT PRIMARY KEY,
pname VARCHAR(20),
price DOUBLE,
category_id VARCHAR(32)
);
INSERT INTO pro`pid``pname``product`duct(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);
-- 1.查询商品名称为“花花公子”的商品所有信息:
SELECT * FROM product WHERE pname = '花花公子';
-- 2.查询价格为800商品
SELECT * FROM product WHERE price = 800;
-- 3.查询价格不是800的所有商品
SELECT * FROM product WHERE price NOT IN (800);
-- 4.查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 600;
-- 5.查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
-- 6.查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price IN (800, 200);
-- 7.查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';
-- 8.查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
-- 9.查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';
-- 10.商品没有分类的商品
SELECT * FROM product WHERE category_id IS NULL;
-- 11.查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL;
查询条件
- 排序
-- 排序(asc 升序、desc 降序)
select 名 字段名 from 名 表名 where 件 条件 order by 字段名
-- 按拼音升序排序(默认按utf-8排序)
order by convert(字段名 uisng gbk) asc
-- 组合排序
select 字段名 from 表名 where 条件 order by 字段名 1[asc/desc],字段名 2[asc/desc]
- 聚合函数
-- 聚合函数
count(列名) -- 统计个数
sum(列名) -- 求一列的总和,只对数值类型操作
avg(列名) -- 求一列的平均值
max(列名) -- 求一列的最大值,字符串也有最大最小值
min(列名) -- 求一列的最小值
- 分组(通常配合聚合函数使用)
select 字段名 from 表名 where 条件 group by 分组字段 [having 过滤的条件]
having和where的区别
where先过滤再分组,where后面不能使用聚合函数。
having先分组再过滤,having可以使用聚合函数。
- limit 限制返回的行数
select 列名 from 表名 where 条件 limit 跳过多少行, 返回的行数
练习
-- 准备数据:
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
chinese FLOAT,
english FLOAT,
math FLOAT
);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
-- 对数学成绩排序后输出
SELECT * FROM student ORDER BY math;
-- 对总分排序后输出,然后再按从高到低的顺序输出
SELECT *,(chinese + english + math) AS total FROM student ORDER BY total DESC;
-- 对姓李的学生成绩排序输出
SELECT *,(chinese + english + math) AS total FROM student WHERE `name` LIKE '李%' ORDER BY total DESC;
-- 统计一个班级共有多少学生?
SELECT COUNT(*) 学生总数 FROM student;
-- 统计数学成绩大于90的学生有多少个?
SELECT COUNT(*) 优秀人数 FROM student WHERE math > 90;
-- 统计总分大于250的人数有多少?
SELECT COUNT(*) FROM student WHERE math + english + chinese > 250;
-- 统计一个班级数学总成绩?
SELECT SUM(math) 数学总成绩 FROM student;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(chinese) 语文总成绩,SUM(math) 数学总成绩,SUM(english) 英语总成绩 FROM student;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(chinese) + SUM(math) + SUM(english) 成绩总和 FROM student;
-- 统计一个班级语文成绩平均分
SELECT AVG(chinese) 语文平均分 FROM student;
-- 求一个班级数学平均分?
SELECT AVG(math) 数学平均分 FROM student;
-- 求一个班级总分平均分
SELECT AVG(chinese) + AVG(math) + AVG(english) 总分平均分 FROM student;
-- 求班级最高分和最低分
SELECT MAX(math + chinese + english) 最高分, MIN(math + chinese + english) 最低分 FROM student;
-- 统计数学成绩前二名到前七名的学生(排序跳过1名学生,取6个学生)
SELECT * FROM student ORDER BY math DESC LIMIT 1,6;
###5. DCL(Data Control Language)
DCL数据库控制语言,可以创建用户和控制用户的权限
创建和销毁用户
- 创建用户
create user '用户名'@'主机名' identified by '密码';
用户名:将创建的用户名。
主机名:指定用户在哪个主机可登录,本地用户用localhost,从任意主机登录可用%。
密码:密码可以为空。
- 销毁用户
drop user '用户名'@'主机名'
授权和撤销权限
- 授权
grant 权限 on 数据库名.表名 to '用户名'@'主机名'
权限:授予用户的权限,如 CREATE、ALTER、SELECT、INSERT、UPDATE、DELETE 等,如果要授予所有的权限则使用 ALL。
‘用户名’@‘主机名’:给哪个用户授权,要加上单引号。与创建用户时的用户名和主机名要相同。
- 撤销授权
drop user '用户名'@' 主机名'
修改root密码
mysqladmin -uroot -p password 新密码
6. 数据库备份与恢复
备份
注:这个操作不用登录,在 bin/mysqldump.exe 文件,在命令行下操作
mysqldump –uroot –proot 数据名 > 文件.sql
恢复
注:这个操作必须登录,在 mysql 中操作
source 文件名.sql;