DDL操作数据库
创建数据库:
create database 数据库名;
举例:create database db1;
判断数据库是否存在,不存在则创建数据库
create database if not exists 数据库名;
举例:create database if not exists db2;
创建数据库并指定字符集
create database 数据库名 character set 字符集;
举例:create database db3 default character set 字符集;
查看所有数据库
show databases;
查看某个数据库的定义信息
show create database db3;
show create database db1;
修改数据库的默认的字符集
alter database db3 character set utf8;
删除数据库的语法
drop database 数据库名;
举例:drop database db2;
查看正在使用的数据库
select database();
使用/切换数据库
use 数据库名;
举例:use db3;
创建表
create table 表名(
字段名1 字段类型1,
字段名2 字段类型2,
。。。。可以叠加 //最后叠加的末尾不能加逗号,前面的都需要加
);
常用的字段类型:
int 整型 double 浮点型 varchar 字符串型 data 日期类型
举例:
create table student (
id int, -- 整数
name varchar(20), -- 字符串
birthday date -- 生日,最后没有逗号
);
查看表
show tables;
查看表结构
desc 表名;
查看创建表的SQL语句
show create table 表名;
具体操作
use day21;//先用哪个数据库
show tables;
查看 student 表的结构
desc student;
创建一个s1表格,和student表格或者之前的表格结构相同
create table s1 like student;
desc s1;//查询一下s1是否复制成功
删除表名
drop table 表名;
判断表名是否存在,如果存在则删除表
drop table if exists 表名;
举例:
-- 直接删除表 s1 表
drop table s1;
-- 判断表是否存在并删除 s1 表
drop table if exists `create`;
与直接删除的区别
如果表不存在,不删除,存在则删除
添加列表add方法
alter table 表名 add 列名 类型;
修改类型modify方法
alter table 表名 modify 列名 新的类型;
修改列名
alter table 表名 change 旧列名 新列名 类型;
删除列
alter table 表名 drop 列名;
修改表名
rename table 表名 to 新表名
举例:rename table student to student2;
修改字符集character set方法
alter table 表名 character set 字符集;
DML:增删该表中的数据
添加数据,语法:
Insert into 表名(列名1,列名2,。。。列名n) values(值1,值2,。。值n)
注意:列名和值一定要一一对应,类型也要对应
如果表名后不写列名,直接表名values(值1,值2,。。值n)此时必须写完每一个类型对应的值,不写就写null
除了数字类型,其他都要用引号括起来,单引号双引号都可以
删除表里面的内容的语法//临时删掉
delete from 哪一行 [WHERE 条件]//重新插入的时候原来的还在
注意事项:
不加任何条件,则删除所有记录,有多少条就会执行多少次删除操作
建议用truncate table 表名;先删除表,然后在创建一个一样的表//在插入的时候就会重新插入
修改数据的语法
update 表名 set 列名1=值1,列名2=值2,。。。[where 条件]
注意:
如果不加条件,将会吧所有记录全部修改
DQL:所有语法介绍
简单查询:
查询表中的所有列:select * from 表名;
查询指定的列:
select 字段1,字段2,。。。 from 表名;
指定列的别名进行查询
select 字段1 AS 别名,字段2 as 别名。。。from 表名;
例如:
-- 使用别名
select name as 姓名,age as 年龄 from student;
-- 表使用别名
select st.name as 姓名,age as 年龄 from student as st
-- 查询去掉重复的记录,本身数据没改变//只去重重复的
select distinct address from student;
某列数据和固定值运算
SELECT 列名 1 + 固定值 FROM 表名;
某列数据和其他列数据参与运算
SELECT 列名 1 + 列名 2 FROM 表名;
举例:
select * from student;
-- 给所有的数学加 5 分
select math+5 from student;
-- 查询 math + english 的和
select * from student;
select *,(math+english) as 总成绩 from student;
-- as 可以省略
注意:不改变原来表中的值
-- 查询 math 分数大于 80 分的学生
select * from student3 where math>80;
-- 查询 english 分数小于或等于 80 分的学生
select * from student3 where english <=80
-- 查询 age 等于 20 岁的学生
select * from student3 where age = 20;
-- 查询 age 不等于 20 岁的学生,注:不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20;
-- 查询 id 是 1 或 3 或 5 的学生
select * from student3 where id in(1,3,5);
-- 查询 id 不是 1 或 3 或 5 的学生
select * from student3 where id not in(1,3,5);
连接查询(多表查询)
内连接
隐式内连接
隐式内连接:看不到 JOIN 关键字,条件使用 WHERE 指定
SELECT 字段名 FROM 左表, 右表 WHERE 条件
显式内连接
显示内连接:使用 INNER JOIN ... ON 语句, 可以省略 INNER
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
左外连接
左外连接:使用 LEFT OUTER JOIN ... ON,OUTER 可以省略
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
用左边表的记录去匹配右边表的记录,如果符合条件的则显示;否则,显示 NULL
可以理解为:在内连接的基础上保证左表的数据全部显示(左表是部门,右表员工)
右外连接
右外连接:使用 RIGHT OUTER JOIN ... ON,OUTER 可以省略
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
用右边表的记录去匹配左边表的记录,如果符合条件的则显示;否则,显示 NULL
可以理解为:在内连接的基础上保证右表的数据全部显示
SELECT 字段1,字段2,… FROM table_1
INNER JOIN table_2 ON table_1.字段x = table_2.字段y;
# INNER JOIN 与 JOIN 是相同的;
# 如table_1中的行在table_2中没有匹配,则不返回
LIKE模糊查询
与“%”一起使用,表示匹配0或任意多个字符
与“_”一起使用,表示匹配单个字符
SELECT * FROM student WHERE stu_name LIKE "李%";//带李字的所有
SELECT * FROM student WHERE stu_name LIKE "李_"//打印出2个字,李开头
SELECT * FROM student WHERE stu_name LIKE "%李%"//包括李
in范围查询
SELECT 字段列1,字段2 ,…FROM 表名 WHERE 字段x IN ( 值1,值2,值3…)
SELECT 字段列1,字段2 ,…FROM 表名 WHERE 字段x IN ( 嵌套一个select判断值语句)
子查询的概念:
1) 一个查询的结果做为另一个查询的条件
2) 有查询的嵌套,内部的查询称为子查询
3) 子查询要使用括号
子查询的结果是一个值的时候
子查询结果只要是单行单列,肯定在 WHERE 后面作为条件,父查询使用:比较运算符,如:> 、<、<>、=
等
SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
子查询结果是多行单列的时候
子查询结果是单例多行,结果集类似于一个数组,父查询使用 IN 运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
子查询的结果是多行多列
子查询结果只要是多列,肯定在 FROM 后面作为表
SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;
子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段
子查询小结
子查询结果只要是单列,则在 WHERE 后面作为条件
子查询结果只要是多列,则在 FROM 后面作为表进行二次查询
排序查询
order by 子句
order by 排序字段1 排序方式1 ,排序字段2 排序方式2.。。。升序,默认
排序方式
*ASC 升序,默认
*DESC 降序 select * from 表名 order by 字段1.。DESC
注意事项:
如果有多个排序条件,则当前面的条件一样时,才会判断第二条件
聚合函数:将一列数据作为一个整体,进行纵向的计算。
1,count:计算个数
一般选择非空列,最好是主键
count(*)sELECT COUNT(列名) from 表名计算个数
2,max:计算最大值select max(列名) from 表名
3,min:计算最小值
4,sum: 计算和
5,avg:计算平均值
注意事项:
聚合函数的计算会排除非空选项,不会计算
有的话解决方法
1:选择不包含非空的列
2:SELECT COUNT(IFNULL(列名,0)) FROM 表名;
分组查询:
语法:group by 分组字段;
注意事项:
1,分组之后查询的字段:分组字段、聚合函数
2,where和having的区别;
1,where在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,通过不满足结构,则不会被查询出来
2,where后不可以跟聚合函数,having可以进行聚合函数的判断
select 列名,聚合函数。。 from where 条件 再执行group by 列名 having 前面的聚合函数限定条件
分页查询
语法:limit 开始的索引,每页查询的条数
公式:开始的索引=(当前的索引-1)*每页显示的条数
select * from 表名 limit(0-n)
select * from 表名 limit a,b a是当前的索引,b是显示的页数
limit b 就直接显示b的页数
连接查询(多表查询)
约束
MYSQL的约束
1;主键约束:primary key
注意:非空且唯一
一张表只能有一个字段为主键
主键是表中记录的唯一标识
create table bm(
id int(4) primary key );
删除主键
alter table bm drop primary key;
创建完表后添加主键
alter table bm modify id int primary key;
完成自动增长
create table bm(
id int(4) primary key auto_increment );
删除自动增长
alter table bm modify id int;
添加自动增长
alter table bm modify id int auto_increment;
2,非空约束:not nul
create table bm(
id int(4) not null );
删除非空约束
alter table bm modify id(4);l
<>非
3,唯一约束:unique,值不能重复
create table bm(
id int(4) unique );
删除唯一约束
alter table bm drop index id;
添加唯一约束
alter table bm modify id int(4) unique;
4,外键约束:foreign key,让表与表产生关系,从而保证数据的准确性
1;在创建表时,可以添加外键
create table bm(
。。。
外键列
constraint 外键名称 foreign key 外键列名称 reference 主表名称(主表列名称)
--删除外键
alter table bm drop foreign key 外键名称;
--已有表,增加外键,并设置级联更新,删除
alter table bm add constraint 外键名称 foreign key 外键列名称 reference 主表名称(主表列名称)on update cascade;or on delect cascade;
常用的函数
常用的数学函数
--数学函数
3 select CEILING ( COUNT(*)/5.0) from News--取大于结果的最小整数
4 select floor ( COUNT(*)/5.0) from News--取小于结果的最大整数
5 select SQRT(2)--数值开平方
6 select ROUND(3.45645,2)
7 select abs(-5):绝对值函数,取此数的绝对值,可以应用于数据库中一些比较乱的值的加减操作。
常用是字符串函数
--字符串函数
10 select LOWER('RGFRG')--大写字母变为小写字母
11 select UPPER('ggfgrt')--小写字母变为大写字母
12 select LTRIM()--去掉左侧字符空格
13 select LEN('chine')--取字符串长度
14 select RTRIM()--去掉字符串右侧空格
15 select LEFT(Title,5)from News--从某列中左侧起截取多少长度的字符串
16 select RIGHT(Title,5)from News--从某列中右侧起截取多上长度的字符串
17 select SUBSTRING(Title,3,5) from News--从某一列中第几个字符开始截取几个字符
18 select REVERSE (Title) from News --翻转某一列
19 select CHARINDEX('中国',Title)from News --查询目标内容在指定区域有没有出现以及出现位置
20 select REPLACE(Title,'中国','美国')from News--替换字符串。例搜索关键字并明显标注
21 select STUFF(Title,3,4,'chine')from News
常用的日期函数
25 select GETDATE()--获取当前时间//select now()
26 select YEAR(sbirthday)from student--取时间年份
27 select month(sbirthday)from student--取日期时间月份
28 select day(sbirthday)from student3--取时间天
29 select datepart(yy,sbirthday)from student4-- yy,mm,dd分别代表年月日
事务
事务
1. 事务的基本介绍
1. 概念:
* 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
2. 操作:
1. 开启事务: start transaction;
2. 回滚:rollback;
3. 提交:commit;
3. 例子:
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 张三给李四转账 500 元
-- 0. 开启事务
START TRANSACTION;
-- 1. 张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2. 李四账户 +500
-- 出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
-- 发现执行没有问题,提交事务
COMMIT;
-- 发现出问题了,回滚事务
ROLLBACK;
4. MySQL数据库中事务默认自动提交
* 事务提交的两种方式:
* 自动提交:
* mysql就是自动提交的
* 一条DML(增删改)语句会自动提交一次事务。
* 手动提交:
* Oracle 数据库默认是手动提交事务
* 需要先开启事务,再提交
* 修改事务的默认提交方式:
* 查看事务的默认提交方式:SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
* 修改默认提交方式: set @@autocommit = 0;
2. 事务的四大特征:
1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
2. 持久性:当事务提交或回滚后,数据库会持久化的保存数据。
3. 隔离性:多个事务之间。相互独立。
4. 一致性:事务操作前后,数据总量不变
3. 事务的隔离级别(了解)
* 概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
* 存在问题:
1. 脏读:一个事务,读取到另一个事务中没有提交的数据
2. 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
3. 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
* 隔离级别:
1. read uncommitted:读未提交
* 产生的问题:脏读、不可重复读、幻读
2. read committed:读已提交 (Oracle)
* 产生的问题:不可重复读、幻读
3. repeatable read:可重复读 (MySQL默认)
* 产生的问题:幻读
4. serializable:串行化
* 可以解决所有的问题
* 注意:隔离级别从小到大安全性越来越高,但是效率越来越低
* 数据库查询隔离级别:
* select @@tx_isolation;
* 数据库设置隔离级别:
* set global transaction isolation level 级别字符串;
* 演示:
set global transaction isolation level read uncommitted;
start transaction;
-- 转账操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;