MySQL
一、概念
什么是数据库
数据库管理系统 (DataBase-ManagementSystem,DBMS) 由一个相互关联的数据的集合和一组用以访问这些数据的程序组成。这个数据集合通常称作数据库(database)
关系型数据库
关系数据库基于关系模型,使用一系列表来表达数据以及这些数据之间的关系。MySQL就是关系型数据库。关系模型已经成为当今主要的数据模型,它比之前的的网络模型和层次模型简化了编程者的工作。现在开始流行的NoSQL,泛指非关系型的数据库。
Mysql配置文件
Ubuntu18上通过apt安装的mysql配置文件及目录是/etc/mysql/mysql.conf.d,
windows上面是安装目录下的my.ini。如果需要改MySQL的一些设置就需要在配置文件里面去改
查看Mysql服务
Linux上,通过:
service mysql status 查看是否开启
service mysql start 开启
service mysql stop 关闭
Windows下通过任务管理器即可查看
注意
本文未按照Mysql标准来书写命令,若对此有要求则具体请参照mysql书写规范配合使用。
以下<> 尖括号内容为命令参数,参数含义可由其中英文意译得之。如:
<username> —— 用户名 <table_name> —— 表名 <field_name> —— 字段名 <constraint_name> —— 约束名
<table_alias> —— 表别名 <field_alias> —— 字段别名 等。
同时每条命令后会接有使用例子,以便理解使用。
二、主要命令
1.连接数据库
本地连接
mysql -u <username> -p <password>
mysql -u admin -p qwe123
远程连接
mysql -h <ip> -p <端口号> -u <username> -p <password>
mysql -h127.0.0.1 -p3306 -u admin -p qwe123
2.操作数据库
查看数据库
show databases;
创建数据库
create database <database_name>;
create database test;
删除数据库
drop database <database_name>;
drop database test;
进入数据库
use <database_name>;
use test;
查看表所在当前数据库
select database();
3.操作数据表
创建数据表(create)
create table <tableName>{
<field_name> <type>,
<field_name> <type>,
···
};
create table MyTable{
id int,
name varchar(20), #varchar(20) 不定长 字符串数据 引号
sex char(20) #char(20) 定长 字符串数据 引号
};
查看数据表(show tables)
show tables;
删除数据表(drop)
drop table <tableName>;
drop table MyTable;
查看表结构(desc)
desc table <tableName>;
desc table MyTable;
show create table <tableName>;
show create table MyTable;
修改表名(alter rename)
alter table <tableName> rename <new_table_name>;
alter table MyTable rename MyNewTable;
4.操作数据表字段
添加字段(alter add)
# 单字段添加
alter table <tableName> add <field name> <type>;
alter table MyTable add age int;
# 多字段添加
alter table <tableName> add (<field_name> <type>, <field_name> <type>, ...);
alter table MyTable add (gender varchar(20), age int, size int);
删除字段(alter drop)
# 单字段删除
alter table <tableName> drop <field_name>;
alter table MyTable drop gender;
# 多字段删除
alter table <tableName> drop <field_name>, drop <field_name>, ..., drop <field_name>;
alter table MyTable drop size, drop age;
修改字段类型(alter modify)
alter table <tableName> modify <field_name> <new_type>;
alter table MyTable modify name char(20);
修改字段类型和名字(alter change)
alter table <tableName> change <field_name> <new_field_name> <new_type>;
alter table MyTable change age ages char(12);
添加字段到指定位置
# 将字段添加到指定字段之后
alter table <tableName> add <new_field_name> <new_type> after <field_name>
alter table MyTable add email varchar(20) after phone
5.操作数据表数据
添加数据(insert into )
# 多个字段添加单个数据
insert into <tableName> (<field_name>, <field_name>, ...) value (<data>, <data>, ...) ;
insert into MyTable (id, name, age, gender) value (1, '猪头', '18', '未知');
# 多个字段添加多个数据
insert into <tableName> (<field_name>, <field_name>, ...) values (<data>, <data>, ...) (<data>, <data>, ...) ...;
insert into MyTable (id, name, age, gender) values (1, '猪头', '18', '未知') (2, '大猪头', '19', '男');
# 不指明字段的时候 默认全字段加数据(插入对应字段数目的数据)
insert into <tableName> value (<field_name>, <field_name>, ...);
insert into MyTable value (1, '猪头', '18', '未知');
删除数据(delete from)
delete from <tableName> where <field_name> = <target_data>;
delete from MyTable where id = 3;
修改数据(update … set)
update <tableName> set <field_name> = <want_data> where <target_field_name> = <target_data>;
update MyTable set age = '13' where id = 2;
查看数据(select … from)
select * from <tableName>;
select * from MyTable;
6.对字段添加约束(建表后)
非空约束(modify)
# 建立非空约束
alter table <tableName> modify <field_name> <type> not null;
alter table MyTable modify name char(20) not null;
# 删除非空约束
alter table <tableName> modify <field_name> <type> null;
alter table MyTable modify name char(20) null;
唯一约束(add)
# 建立唯一约束
alter table <tableName> add unique(<field_name>);
alter table MyTable add unique(id);
# 删除唯一约束
alter table <tableName> drop index <field_name>;
alter table MyTable drop index id;
主键约束(add)
注意,一张表只允许出现一个主键约束
# 建立主键约束
alter table <tableName> add primary key(<field_name>);
alter table MyTable add primary key(id);
# 删除主键约束
alter table <tableName> drop primary key;
alter table MyTable drop primary key;
自增约束(change)
关键字为 AUTO_INCREMENT 自动编号
注意必须与主键组合使用,默认情况下起始值为1,每次的增量为1
# 建立自增约束
alter table <tableName> change <field_name> <field_name> <type> not null auto_increment;
alter table MyTable change id id int not null auto_increment;
# 删除自增约束(即删除auto_increment关键字即可)
alter table <tableName> change <field_name> <field_name> <type> not null;
alter table MyTable change id id int not null;
默认约束(set)
# 建立默认约束
alter table <tableName> alter <field_name> set default <default_data>;
alter table MyTable alter gender set default "未知";
# 删除默认约束
alter table <tableName> modify <field_name> <type>;
alter table MyTable alter gender varchar(20);
7.对字段添加约束(建表前)
create table <tableName>{
<field_name> <type> <constraint_name> <constraint_name> ...,
<field_name> <type> <constraint_name> <constraint_name> ...,
<field_name> <type> <constraint_name> <constraint_name> ...,
...
}auto_increment = <initial_val>, charset = <initial_charset>;
create table MyTable{
id int primary key auto_increment,
name varchar(20) not null,
age int default 18,
sex varchar(20) default '男',
phone char(11) unique key
}auto_increment = 0, charset = UTF8;
8.表关系(即添加相关外键约束)
表关系较为复杂,以下将用例子表述,以下注释为python写法,用以解释说明,直接复制会报错。
外键约束
之前讲到的约束,都是表内约束自身,而外键约束约束其他表。
创建条件
- 数据表的存储引擎只能为InnoDB
- 外键列和参照列数据类型一致
- 外键必须关联到键上面去
- 外键名在数据库要唯一
查看所有外键
select constraint_name from information_schema.key_column_usage where constraint_schema = 'mydb' and constraint_name != 'primary'
添加外键的格式
alter table <tableName> add foreign key (<field_name>) references <other_table_name>(<other_field>) on delete <> on update <>
on delete 及 on update 根据需要可省略
ondelete 及 on update 的添加选项
- CASCADE: 删除包含与已删除键值有参照关系的所有记录(级联删除)
- SET NULL: 修改包含与已删除键值有参照关系的所有记录,使用NULL值替换(只能用于已标记为NOT NULL的字段)
- RESTRICT: 拒绝删除要求,直到使用删除键值的辅助表被手工删除,并且没有参照时(这是默认设置,也是最安全的设置)
- NO ACTION: 啥也不做
一对多关系
一对多与多对一是一个概念,指的是一个实体的某个数据与另外一个实体的多个数据有关联关系。
学校中一个学院可以有很多的学生,而一个学生只属于某一个学院(通常情况下),学院与学生之间的关系就是一对多的关系,通过外键关联来实现这种关系
# 一对多的关系 一个学院对多个学生
# 学院表
create table department (
d_id int primary key auto_increment, # 学院id 设置主键约束,自增约束
d_name varchar(20) not null # 学院name 设置非空约束
);
#学生表
create table students (
s_id int primary key auto_increment, # 学生id 设置主键约束,自增约束
s_name varchar(20) not null, # 学生name 设置非空约束
department_id int, # 该学生的学院id
# 设置外键关联,此处fk_ds为约束名,意为foreignkey_departmentstudent,可自定义,on delete set指定删除方式。
constraint fk_ds foreign key (department_id) references department (d_id) on delete set null
);
建立方法
<field_from_this_table> <type>,
constraint <foreignkey_name> foreign key (<field_from_this_table>) references <other_table> (<field_from_other_table>) on delete <dl_way>
一对一关系
指的是一个实体的某个数据与另外一个实体的一个数据有关联关系。
学生表中有学号、姓名、学院,但学生还有些比如电话,家庭住址等比较私密的信息,这些信息不会放在学生表当中,会新建一个学生的详细信息表来存放。这时的学生表和学生的详细信息表两者的关系就是一对一的关系,因为一个学生只有一条详细信息
#学生表
create table students (
s_id int primary key auto_increment, # 学生id 设置主键约束,自增约束
s_name varchar(20) not null, # 学生name 设置非空约束
department_id int, # 创建该学生的学院id,用于与学院做关联。
# 设置外键关联,此处fk_ds为约束名,意为foreignkey_departmentstudent,可自定义,on delete set指定删除方式。
constraint fk_ds foreign key (department_id) references department (d_id) on delete set null
);
#学生详情表
create table stu_detail (
sd_id int primary key auto_increment,
sd_age int,
sd_sex char(20) default 'nan',
sd_tel char(11) unique key,
student_id int unique key, # 创建该学生详情表对应的学生id,设定唯一约束
# 设置外键关联,此处fk_ss为约束名,以为foreignkey_studentstudentDetail,可自定义,on delete set指定删除方式。此处为级联删除
constraint fk_ss foreign key (student_id) references students (s_id) on delete set cascade
);
建立方法
<field_from_this_table> <type> unique key, # 设定唯一约束
constraint <foreignkey_name> foreign key (<field_from_this_table>) references <other_table> (<field_from_other_table>) on delete <dl_way>
多对多关系
一个实体的数据对应另外一个实体的多个数据,另外实体的数据也同样对应当前实体的多个数据。
学生要报名选修课,一个学生可以报名多门课程,一个课程有很多的学生报名,那么学生表和课程表两者就形成了多对多关系。对于多对多关系,需要创建第三张关系表,关系表中通过外键加主键的形式实现这种关系。
这里需要注意的是,在多对多关系中,我们常用中间表来表示。
#学生表
create table students (
s_id int primary key auto_increment, # 学生id 设置主键约束,自增约束
s_name varchar(20) not null, # 学生name 设置非空约束
department_id int, # 创建该学生的学院id,用于与学院做关联。
# 设置外键关联,此处fk_ds为约束名,意为foreignkey_departmentstudent,可自定义,on delete set指定删除方式。
constraint fk_ds foreign key (department_id) references department (d_id) on delete set null
);
# 课程表
create table course (
c_id int primary key auto_increment,
c_name varchar(20) not null
);
#中间表
create table stu_course (
s_id int,
c_id int,
primary key(s_id ,c_id) # 建立联合主键
constraint fk_s foreign key (s_id) references students (s_id),
constraint fk_c foreign key (c_id) references course (c_id)
);
建立方法
# 创建中间表
create table <tableName>{
<field_name_1> type,
<field_name_2> type,
primary key(<field_name_1>, <field_name_2>), # 建立联合主键
constraint <foreignkey_name1> foreign key (<field_name_1>) references <other_table1>(<field1_from_other_table>),
constraint <foreignkey_name2> foreign key (<field_name_2>) references <other_table2>(<field2_from_other_table>)
};
删除外键约束
alter table <tableName> drop foreign key <foreign_key_name>;
alter table students drop foreign key fk_ds;
9.事务
含义
数据库事务(transaction)是访问并可能操作各种数据项的一个数据库操作序列,这些操作要么全部执行,要么全部不执行,是一个不可分割的工作单位。
简单来说:几个操作是绑定在一起的,要么都做完,要么一个都不做。
完成一个操作的所有步骤序列
# 开启事务
begin
# 回滚
rollback
# 结束事务
commit
事务只针对数据处理做操作,不对表做操作
10.查询
基本查询
一般来说,查询所有字段是最耗时长的,所以今后查询数据尽可能按需所取。查询字段尽可能查询部分字段,不需要的字段就要不查询出来。
# * 表示所有字段
# 查询该表所有字段
select * from <tableName>;
select * from MyTable;
# 根据字段查询数据
select <field_name> from <tableName>;
select age from MyTable;
条件查询
查询条件可以是大于等于不等于(>,=,<>)等等,也可以是更加复杂的判断都是可以的。
# where
select <field_name> from <tableName> where <target_field_name> = <target_val>;
select name from MyTable where id = 2;
# and or not(除了等号同样可用大于小于号)
select <field_name> from <tableName> where <target_field_name1> = <target_val1> and <target_field_name2> = <target_val2>;
select name from MyTable where id = 2 and age = '18';
select <field_name> from <tableName> where <target_field_name1> = <target_val1> or <target_field_name2> = <target_val2>;
select name from MyTable where id = 1 or age = '18';
select <field_name> from <tableName> where <target_field_name1> > <target_val1> and not <target_field_name2> = <target_val2>;
select name from MyTable where id > 3 and not age = '18';
取别名
如果列名或者表名太长,可以给它们取一个别名,可以方便取使用。实际上 as 可以省略,但是为了方便阅读,我们通常会加上。
select <field_name> as <field_alias> from <tableName> as <table_alias>;
select name as a from MyTable as mt;
模糊查询
# 关键字 like
select * from <tableName> where <field_name> like <like_data>;
select * from MyTable where name like '%o%';
多表查询
内连接
以下直接用例子来表述
把两张表想象成X轴和Y轴,轴上的每个点,就是两个表里面的每行数的集合。
SELECT * FROM `department` INNER JOIN `students`;
SELECT * FROM `department` , `students`;
SELECT * FROM `department` CROSS JOIN `students`;
SELECT * FROM `department` JOIN `students`;
以上这个四个SQL的结果都一样,都是笛卡尔积,也叫无条件连接/交叉连接等。注意在mysql中,INNER JOIN 和 CROSS JOIN 是一样的。
SELECT * FROM `department` INNER JOIN `students` ON `department`.`d_id` = `students`.`department_id`;
SELECT * FROM `department` as p , `students` as s WHERE p.`d_id`=s.`department_id`;
SELECT * FROM `department` as p CROSS JOIN `students` as s WHERE p.`d_id` = s.`department_id`;
以上三条为使用别名的查询方法,同样结果一样
左连接
A表 LEFT JOIN B表 会以左边的表为主,展示左边表的所有数据,展示右边表中符合ON子句中条件的数据,没有为空
SELECT * FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`student_id`;
右连接
右连接和左连接类似,只是作用相反。
SELECT * FROM `students` s RIGHT JOIN `student_details` sd ON s.`s_id`=sd.`student_id`;
全连接
UNION 用于合并两个或多个 SELECT 语句的结果集,并消去表中任何重复行。
SELECT s.`name`,c.`name` FROM `students` AS s LEFT JOIN `select` AS se ON s.`s_id`=se.`stu_id` LEFT JOIN `course` c ON c.`id`=se.`coures_id`
UNION SELECT s.`name`,c.`name` FROM `students` AS s LEFT JOIN `select` AS se ON s.`s_id`=se.`stu_id` LEFT JOIN `course` c ON c.`id`=se.`coures_id`
子表查询
在一个SQL语句中出现两个SQL语句,就是子表查询,下面这个子表查询是在JOIN这个地方建立查询。
SELECT
s.`name`,
e.`name`
FROM
`students` AS s
LEFT JOIN(
SELECT
se.`stu_id`,
c.`name`
FROM
`select` AS se
JOIN `course` c ON se.`coures_id` = c.`id`
) AS e ON s.`s_id`=e.`stu_id`;
子查询不但可以放在JOIN 哪里,也可以放在 WHERE 后面
SELECT
*
FROM
`students` AS s
WHERE
s.`dept_id` = (
SELECT
`id`
FROM
`department` AS d
WHERE
d.`name` = '外国语学院’
);
查询结果排序
对查询出来的结果进行排序,ASC升序(默认) DESC降序。
SELECT * FROM `student` AS s JOIN `student_details` AS sd ON s.`s_id`=sd.`stu_id` ORDER BY s.`s_id` DESC;
查询结果行数限制
对查询出来的结果限制显示的行数。
SELECT * FROM `students` s JOIN `student_details` sd ON s.`s_id`=sd.`stu_id` ORDER BY s.`s_id` DESC LIMIT 3;
分组查询
分组是个常见的操作,常用于分组统计,使用GROUP BY后,会按照GROUP BY后面的字段进行分组,且必须是明确的字段,不能是* ,因此SELECT后面也不能是* .其次可以使用 HAVING 可以对分组之后的结果进行筛选,注意:HAVING 后的字段必须是SELECT后出现过的
SELECT d.`d_id`,d.`name`,COUNT(*) FROM `department` AS d LEFT JOIN `students` AS s ON d.`d_id`=s.`dept_id` GROUP BY d.`d_id`,d.`name`;
SELECT d.`d_id`,d.`name`,COUNT(*) FROM `department` AS d LEFT JOIN `students` AS s ON d.`d_id`=s.`dept_id` GROUP BY d.`d_id`,d.`name` HAVING COUNT(*)>1;
11.部分mysql函数
字段处理
IFNULL()
SELECT s.`name`,IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`; #处理NULL
DISTINCT
SELECT DISTINCT `name` FROM `students` ; # 字段去重
字符串截取
字符串截取,left是从左边开始截取,right是从右边开始截取,SUBSTRING可以指定截取范围。
LEFT()
SELECT LEFT(s.`name`,2),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
RIGHT()
SELECT RIGHT(s.`name`,2),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
SUBSTRING()
SELECT SUBSTRING(s.`name`,2,5),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
字符串拼接
CONCAT()
SELECT CONCAT(s.`name`,sd.`id_card`) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
类型转换
CAST()
SELECT CAST(sd.`id_card` AS CHAR) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
CONVERT()
SELECT CONVERT(s.`dept_id`,SIGNED) FROM `students` s ;
时间函数
DAY()
SELECT DAY('2017-08-18')-DAY('2017-08-01’);
NOW()
SELECT NOW();
ROM students
s LEFT JOIN student_details
sd ON s.s_id
=sd.stu_id
;
##### SUBSTRING()
```mysql
SELECT SUBSTRING(s.`name`,2,5),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
字符串拼接
CONCAT()
SELECT CONCAT(s.`name`,sd.`id_card`) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
类型转换
CAST()
SELECT CAST(sd.`id_card` AS CHAR) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
CONVERT()
SELECT CONVERT(s.`dept_id`,SIGNED) FROM `students` s ;
时间函数
DAY()
SELECT DAY('2017-08-18')-DAY('2017-08-01’);
NOW()
SELECT NOW();
MySQL的函数还有很多,许多也和我们平常用的一样,比如ABS,MAX,MIN,ROUND,AVG,SUM等等,用法也是一样的,如果有需求,直接查下资料就可以。