MySQL的常用命令及语法
一、对数据库的操作
1.1 创建数据库
create database 库名;
create database 库名 charset=编码集;
1.2 删除数据库
drop database 库名;
1.3 使用数据库(切换数据库)
use 库名;
1.4 查看当前正在操作的库
select database();
1.5 查看所有数据库
show databases;
1.6 查看数据库创建语句
show create database 库名;
1.7 查看数据库编码
show variables like '%char%';
1.8 修改数据库编码格式
alter database 库名 character set utf8;
1.9 修改中文乱码
set names 编码;
1.10 导入导出数据库
https://www.cnblogs.com/yuwensong/p/3955834.html
1.11 数据库数据换表存储
1. 表结构完全一样
insert into 表1 select * from 表2
2. 表结构不一样(这种情况下得指定列名)
insert into 表1 (列名1,列名2,列名3) select 列1,列2,列3 from 表2
3、只从另外一个表取部分值
insert into 表1 (列名1,列名2,列名3) values(列1,列2,(select 列3 from 表2));
需求:从另外一个表去部分值(多条数据)。其他是数据固定值。
4、INSERT INTO 表1 ( 列名1, 列名2, 列名3 ) SELECT 固定值1,固定值2,列名3 FROM 表2 ;
二、对数据库表的操作
1.创建表
create table 表名(列名1 类型(长度) 约束,列名2 类型(长度) 约束);
--约束:
主键:primary key
外键:foreign key,https://www.yiibai.com/mysql/foreign-key.html
唯一:unique
不为空:not null
无符号:unsigned
默认值:default
自增:auto-increment
补零:zerofill
--字段类型
数值类型:
类型
大小
范围(有符号)
范围(无符号)
用途
TINYINT
1 byte
(-128,127)
(0,255)
小整数值
SMALLINT
2 byte
(-32768,32767)
(0,65535)
大整数值
MEDIUMINT
3 byte
(-8388608,8388607)
(0,16777215)
大整数值
INT或INTEGER
4 byte
(-2147483648,2147483647)
(0,4294967295)
大整数值
BIGINT
8 byte
(...,...)
(...,...)
极大整数值
FLOAT
4 byte
(...,...)
(...,...)
单精度浮点数
DOUBLE
8 byte
(...,...)
(...,...)
双精度浮点数
字符串类型:
类型
大小
说明
CHAR
0-255字节
定长字符串(char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉,所以char类型字符串末尾不能有空格,char类型的字符串检索速度要比varchar类型的快。)
VARCHAR
0-65535 字节
变长字符串(char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),所以varchar(4),存入3个字符将占用4个字节。 )
TINYBLOB
0-255字节
不超过 255 个字符的二进制字符串
TINYTEXT
0-255字节
短文本字符串
BLOB
0-65 535字节
二进制形式的长文本数据
TEXT
0-65 535字节
长文本数据(text类型不能有默认值。varchar查询速度快于text,在都创建索引的情况下,text的索引似乎不起作用,.varchar可指定n,text不能指定n,text是实际字符数+2个字节)
MEDIUMBLOB
0-16 777 215字节
二进制形式的中等长度文本数据
MEDIUMTEXT
0-16 777 215字节
中等长度文本数据
LONGBLOB
0-4 294 967 295字节
二进制形式的极大文本数据
LONGTEXT
0-4 294 967 295字节
极大文本数据
日期时间类型:
类型
大小
范围
格式
说明
DATE
3字节
1000-01-01-9999-12-31
YYYY-MM-DD
日期值
TIME
3字节
'-838:59:59'-838:59:59'
HH:MM:SS
时间值或持续时间
YEAR
1字节
1901-2155
YYYY
年份值
DATETIME
8字节
1000-01-01 00:00:00-9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
混合日期和时间值
TIMESTAMP
4字节
1970-01-01 00:00:00-2038
YYYYMMDD HHMMSS
混合日期和时间值,时间戳
二进制类型:
布尔:bit
bit 表示1个二进制的位
bit(8) 表示8个二进制的位
性别可以定义为0,1, 而不使用male或female字符串
数据逻辑删除
所有基于两种状态的数据都可以使用0,1来存储.
2 查看数据库表
2.1 查看库里所有的表
show tables;
2.2 查看单个表的结构
desc 表名;
3 删除数据库中的表
drop table 表名;
4 修改表
4.1 添加一列
alter table 表名 add 列名 类型(长度) 约束 ;
4.2 修改列的类型
alter table 表名 modify 要修改的列名 类型(长度) 约束;
4.3 修改类的列名及类型
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
4.4 删除表的列
alter table 表名 drop 列名;
4.5 修改表名
rename table 表名 to 新表名;
4.6 查看表的字符集
show create table
4.7 修改表的字符集
alter table 表名 character set 编码;
4.8 查看字段的编码
show full columns from
4.9 修改字段的编码
alter table modify column character set utf8 collatr 排序规则;
三、对数据表中的数据进行操作
1 插入记录
指定列插入:insert into 表名 (列名1,列名2,......) values (值1,值2,......)
全值插入:insert into 表名 values (值1,值2,......)
2 修改表记录
不带条件的:update 表名 set 列名=值,列名=值......
带条件的:update 表名 set 列名=值,列名=值...... where 条件语句
3 删除表记录
不带条件的:delete from 表名;(不带条件会删除所有表中记录)
带条件的:delete from 表名 where 条件
四 查询操作
1 简单查询
查询所有:select * from 表名; ------>等同于 :select 表名.* from 表名;(这是完整形式的SQL语句)
查询指定列:select 列名1,列名2...... from 表名
查询指定列并使用别名:select 列名 as 别名 from 表名
去掉重复值:select distinct 列名 from 表名;
排序:select * from 表名 order by 列名 asc->升序(desc->降序);
2 条件查询 where
select * from 表名 where 条件;
------>条件种类
比较运算符(大于>,小于
逻辑运算符(与:and,或:or,非:not)
子查询:select...where(select...)
3 模糊查询 like
select * from 表名 where 列名 like 条件;
-------->条件种类:
% 表示任意字符,匹配零个或多个。
_ 表示任意字符,一个_只能匹配一个。
4 范围查询
in 表示在一个非连续的范围内:
select * from 表名 where age in(10,18); 匹配年龄是10或18的。
select * from 表名 where age not in(10,18); 匹配年龄不是10或18的。
between ... and ...表示在一个连续的范围内 两边都会包含:
select * from 表名 where age between 10 and 20;匹配年龄是10到20之间的。
select * from 表名 where age not between 10 and 20; 匹配年龄不是10到20之间的。
判断 null 不能够使用比较运算符 应该使用is:
select * from 表名 where age is null;匹配年龄为空的。
select * from 表名 where age is not null;匹配年龄为空的。
五 聚合函数
1 统计总数的:count()
统计表中数据的总数:select count(*) from 表名;
统计满足条件的数据总数:select count(*) from 表名 where 条件;
2 最大值:max() 最小值min()平均值avg()
查询所有数据中某个列的最大值:select max(列名) from 表名 ;
查询满足条件的数据中某个列的最小值:select min(列名) from 表名 where 条件;
3 平均值avg()
计算年龄的平均值:select avg(age) from 表名;
四舍五入 round(123.23 , 1) 保留1位小数, 四舍五入:
计算所有人的平均年龄,保留2位小数:select round(avg(age),2) from 表名;
4 在sql 中如何查看帮助文档 ?
- 如何查看函数帮助文档 ? functions;
- ? create, 或者 ? insert 等等都可以查询相关帮助文档;
六 分组
目的:为了进行聚合统计
按照某个列名进行分组:select 列名 from 表名 group by 列名;
计算每种性别中的人数, 聚合函数会作用在分组之后的数据:
select gender,count(*) from 表名 group by gender;
对应的查询方式: 一个性别对应一个名字:
select gender name from 表名 group by gender;
一种性别对应这个性别下所有的名字:需要使用group_concat。
select gender, group_concat(name) from students group by gender;
分组后进行条件筛选需要用having
where 是对源数据做筛选操作
having 是对分组之后的数据做进一步的筛选操作, 有having 就一定有group by 有 group by 不一定有having
七 分页
select * from 表名 limit 0,4
//limit start,count
start:表示从哪里开始查询,startstart 默认值为0, 可以省略, 代表跳过多少条数据
count:代表 查询多少条
八 连接查询
定义:将两个表按照某种条件合并到一起
笛卡尔积查询:会产生很多无用的信息 select 表1.列名,表2.列名 from students,classes;
select * from 表1,表2 表1.cls_id = 表2.id;
连接查询 将两个表中的数据按照设置的连接条件进行筛选, 符合连接条件的数据才能够被筛选出来
内连接查询:表1 inner join 表2 on 设置内连接条件
内连接的其他写法:
select * from students join classes on students.cls_id = classes.id;
select * from students cross join classes on students.cls_id = classes.id; 外连接查询: left join + right join
select * from students left outer join classes on students.cls_id = classes.id;
九 自关联
自关联:自己关联自己 a inner join a ,无限的向下分级的业务可以使用自关联 例:查询出表areas中广东省有哪些市 select p.atitle,c.atitle from areas as p inner join areas as c on p.aid=c.pid where p.atitle="广州省";
十 数据库的导入与导出
10.1 导出整个数据库
mysqldump -u用户名 -p 数据库名> 路径+导出的文件名
10.2 导出一张表
mysqldump -u 用户名 -p 数据库名 表名 > 路径+导出的文件名
10.3 导入:通过xxx.sql 文件将数据恢复到指定的数据库
第一步:在某台mysql服务下创建数据库 create database 库名 charset=utf8;
第二步:将数据恢复到指定数据库
mysql -uroot -p 创建的数据库名称 < 路径/文件名
十一 数据库的设计
E-R模型:entry(实体)-relationship(关系)
--1 对 1
--1 对 多
--多 对 多
三范式:
◆ 第一范式(1NF):强调的是列的原子性,即列不能够再分成其他几列。
◆ 第二范式(2NF):首先是 1NF,另外包含两部分内容,一是表必须有一个主键
◆ 第三范式(3NF):首先是 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。
十二 视图
1.定义视图:建议以v_开头
create view 视图名 as select语句 ;
2.查看视图:show tables;
3.使用视图:select * from 视图名称;
4.删除视图:drop view 视图名称;
十三 事务
事务的四大特性:
原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
1. 开启事务:begin 或 start transaction
2. 提交事务:commit
3. 回滚事务:rollback