目录
数据库的安装和配置
安装及配置参考:https://www.runoob.com/mysql/mysql-install.html
服务启动及用户设置参考:MySQL 管理 | 菜鸟教程
数据库操作
登陆及登出
远程登陆:mysql -h 主机名 -u 用户名 -p 密码
本机登陆:mysql -u 用户名 -p 密码
退出数据库:exit 或者 quit
库操作
展示所有数据库:show databases;
创建数据库:create database [if not exists] 数据库名; ----添加[if not exists]的作用,如果创建的数据库已经存在不会报错。
删除数据库:drop database [if exits] 数据库名; -----添加[if exists]的作用同上。
选择使用指定数据库:use 数据库名;
表操作
展示当前数据库中的所有表
show tables;
查看表结构及属性
desc 表名;
describe 表名;
show create table 表名;
show columns from 表名;创建表
create table [if not exists] 表名 (表数据字段及字段属性); ----标准数据类型参考:MySQL 数据类型 | 菜鸟教程
修改表
修改表名:alter table 表名 rename to 新表名;
修改字段名:alter table 表名 change 旧字段名 新字段名 字段属性;
修改字段类型:alter table 表名 modify 字段名 字段属性;
添加字段:alter table 表名 add 字段名 字段属性;
删除字段:alter table 表名 drop 字段名;删除表
drop table 表名;
表复制
- 只复制表结构到新表 :CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2;
- 复制表结构及数据到新表:CREATE TABLE 新表 SELECT * FROM 旧表;
- 复制旧表的数据到新表(表结构不一致时):INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表;
- 复制旧表的数据到新表(表结构一致时):INSERT INTO 新表 SELECT * FROM 旧表;
数据操作
增删改查
插入数据
单行插入:insert into 表名 (字段名) values (值); ——可以指定多字段插入
全字段插入:insert into 表名 values (值);
多行插入:insert into 表名 (字段名) values (line1值),(line2值),...,(lineN值);
查询数据
整表查询:select * from 表名;
指定字段查询:select 字段名 from 表名;
条件过滤查询:select 字段名 from 表名 where 过滤条件;
修改数据
修改全字段数据:update 表名 set 字段名=值;
修改多个字段数据:update 表名 set 字段1=值1,字段2=值2,....;
条件过滤修改:update 表名 set 字段1=值1,字段2=值2,.... where 过滤条件;
删除数据
删除全表数据:delete from 表名;
删除指定条件表数据:delete from 表名 where 过滤条件;数据操作的条件语句
条件语句where
表别名:对于比较长的数据表可以使用取别名后使用,方法如下:
表名 as 别名
表名 别名两种不同的删除表数据的方法:delete 和 truncate
- 效率上truncate比delete快,但truncate删除后不记录mysql日志,不可以恢复数据。
- truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表。
如删除一个带有主键的表数据,使用delete删除完之后,插入数据时仍然不能插入这个存在过的主键名,即使查找不到它,这时候就需要使用truncate进行删除。
truncate table 表名,可以重置表id
条件运算符
比较运算: 等于= 、不等于!= 或<> 、大于> 、 小于< 、 大于等于>= 、 小于等于<= 、空值 is null 、非空is not null
逻辑运算: and 、 or 、 not
排序:order by (默认为正序asc,倒序:desc)
select * form 表名 order by 序列名 desc;
数据数量限制:limit start,count
select * form 表名 limit start; —— 查询前strat条数据
select * form 表名 limit start,count; ——查询从start+1条开始共count条数据
去重:distinct 字段名
select distinct * from 表名;
模糊查询:like
‘%’或‘_’ —— % :任意多个字符;_ :任意一个字符
select * from 表名 where 字段名 like ‘x%x_’;正则匹配:regexp (not regexp) 或rlike (not rlike) ——查考:https://www.runoob.com/sql/sql-wildcards.html
^ 非运算:select * from 表名 where 字段名 (not) rlike ‘^[xx]’;
!同^ :[!xxx] 或者[^xxx] 不在字符列中的任意字符
[] 括号中的任意一个字符
范围查询: (not) between
连续范围: select * from 表名 字段名 between n and m;成员运算:in 或者 (not in)
select * from 表名 字段名 (not) in (字段值1,字段值2......);
函数
统计:count(字段名)
平均值:AVG(字段名)
最大值:max(字段名)
最小值:min(字段名)
求和:sum(字段名)
列出字段全部值:group_concat(字段名)
提取字段字符:mid(字段名,start,length) —— start :起始位置,必填,起始值为1,length,默认为全部长度
字符长度:length(字段名)
四舍五入:round(字段名,decimals) —— decimals为保留小数的位数,不指定的时候默认为0,返回四舍五入后的整数。
返回当前系统时间:now() 括号中可以指定参数0-6,不指定默认为0,表示秒的级别
将字母小写:lcase(字段名)
将字母大写:ucase(字段名)
条件筛选:having 筛选条件 放到语句的最后面使用
select * from 表名 having 筛选条件;
在一个语句中同时出现where 、聚合函数、having 时,执行的顺序是先where,再聚合函数和别名,最后执行having子查询:select 语句嵌套,使用圆括号将子查询的select语句作为查询条件使用
连接查询
笛卡尔积:select * from 表1,表2,.....;
分组查询:group by
select avg(字段名) from 表名 group by 分组字段名;
可以联合条件过滤使用:select sum(字段名) from 表名 group by 分组字段名 having 过滤条件;
内连接
无条件内连接:将每张表中的数据一一进行组合,结果同笛卡尔连接
select * from 表1 inner join 表2;
有条件内连接:在无条件内连接的基础上加上on子句
select * from 表1 inner join 表2 on 条件;
外连接
左外连接:以左表为基准,保留左表中的数据,右表中空的数据用NULL填充
select * from 表1 left join 表2 on 连接字段(表1.字段名=表2.字段名);
右外连接:以右表为基准,保留右表中的数据,左表中空的数据用NULL填充
select * from 表1 right join 表2 on 连接字段(表1.字段名=表2.字段名);
条件约束
通过对表数据结构属性的限制,让表中的数据具有完整性和唯一性。
默认值约束default:插入数据的时候,如果没有明确为字段赋值,则自动赋予默认值,在没有设置默认值的情况下,默认值为NULL
非空约束NOT NULL:限制字段的插入值不能为空,空字符不等于null
唯一约束unique key :限制字段的值不能重复,确保字段有唯一的值
主键约束primary key:通常每张表里面都需要有一个主键体现唯一性,主键约束具有非空+唯一的共同作用
自增长约束auto_increment:自动编号,一般和主键联合使用,一张表里面只能有一个自增长
外键约束foreign key:保持两张及以上表的数据一致性。被参照表的参照数据不能删除和修改,外键字段数据只能是被参照数据表中的关联字段数据。
表关系
一对一:将两张表中的主键和主键相关联,关联的方法是使用外键关联将两张表的主键关联起来。
一对多:通过外键关联的方法将两张表关联在一起,外键选择不要为当前表的主键,不然会产生一对一的关系。
多对多:需要引入一张中间表的,中间表需要将其他相关联的表通过创建多个外键的方式关联起来。