MySQL
一、可以把 SQL 分为两个部分:数据操作语言 (DML) 和 数据定义语言 (DDL)。
DML:
select- 从数据库表中获取数据
update - 更新数据库表中的数据
delete - 从数据库表中删除数据
insert into- 向数据库表中插入数据
DDL:
create database - 创建新的数据库
alter database - 修改数据库
create table - 创建新表
alter table - 变更数据库表
drop table - 删除表
create index - 创建索引(搜索键)
drop index - 删除索引
- SQL 是一门特殊的语言,专门用来操作关系数据库 不区分大小写
- 对于web程序员来讲,重点是数据的crud(增删改查),必须熟练编写DQL、DML,能够编写DDL完成数据库、表的操作,其它语言如TPL、DCL、CCL了解即可
TPL:
事务处理语言,对事务进行处理,包括begin transaction、commit、rollback
DCL:
数据控制语言,进行授权与权限回收,如grant、revoke
CCL:
指针控制语言,通过控制指针完成表的操作,如declare cursor
DDL:
数据定义语言,进行数据库、表的管理等,如create、drop
数据类型
- 使用数据类型的原则是:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间
常用数据类型如下:
(1),整数:int, bit
(2),小数:decimal
decimal表示浮点数,如decimal(5,2)表示共存5位数,小数占2位
(3),字符串:varchar,char
char表示固定长度的字符串,如char(3),如果填充'ab'时会补一个空格为'ab '
varchar表示可变长度的字符串,如varchar(3),填充'ab'时就会存储'ab'
字符串text表示存储大文本,当字符大于4000时推荐使用
(4),日期时间:date,time,datetime
(5),枚举类型:(enum)
对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径
约束
主键primary key: 物理上存储的顺序
非空not null: 此字段不允许填写空值
唯一unique: 此字段的值不允许重复
默认default: 当不填写此值时会使用默认值,如果填写时以填写的为准
外键foreign key: 对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常
说明: 虽然外键约束可以保证数据的有效性,但是在进行数据的crud(增加、修改、删除、查询)时,都会降低数据库的性能,所以不推荐使用,那么数据的有效性怎么保证呢?答:可以在逻辑层进行控制
MySQL
启动服务:sudo service mysql start
重启服务: sudo service mysql restart
停止服务: sodo service mysql stop
数据库操作
1,查看所有数据库
show databeses;
2,使用数据库
use 数据库名;
3,查看当前使用的数据库
select database();
4,创建数据库
create database 数据库名 charset=utf8;
5,删除数据库
drop database 数据库名;
数据表操作
1,查看当前数据库中所有表
show tables;
2,查看表结构
desc 表名;
3,创建表 (auto_increment表示自动增长)
例:班级表
mysql> create table classes(
-> id int unsigned auto_increment primary key not null,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.00 sec)
例:学生表
mysql> create table students(
-> id int unsigned auto_increment primary key not null,
-> name varchar(20) default '',
-> age tinyint unsigned default 0,
-> height decimal(5,2),
-> gender enum('男','女','中性','保密'),
-> cls_id int unsigned default 0
-> );
Query OK, 0 rows affected (0.02 sec)
4,修改表-添加字段
alter table 表名 add 列名 类型;
mysql> alter table students add birthday datetime;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
5,修改表-修改字段(重命名)
alter table 表名 change 原名 新名 类型及约束;
mysql> alter table students change birthday birth datetime not null;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
5,修改表-修改字段(不重命名)
alter table 表名 modify 原名 新名 类型及约束;
mysql> alter table students modify birth date not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
6,修改表-删除字段
alter table 表名 drop 列名;
mysql> alter table students drop birth;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
7,删除表
drop table 表名;
mysql> drop table classes;
Query OK, 0 rows affected (0.00 sec)
8,查看表的创建语句
show create table 表名;
mysql> show create table students;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT '',
`age` tinyint(3) unsigned DEFAULT '0',
`height` decimal(5,2) DEFAULT NULL,
`gender` enum('男','女','中性','保密') DEFAULT NULL,
`cls_id` int(10) unsigned DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
数据增删改查:创建(Create)、更新(Update)、读取(Retrieve)、删除(Delete)
查询
1,查询所有列
select * from 表名;
mysql> select * from students;
Empty set (0.00 sec)
1,查询所有列
select * from 表名;
mysql> select * from students;
Empty set (0.00 sec)
2,查询指定列(可以使用as为列或表起别名)
select 列1,列2 from 表名;
mysql> select id,name from students;
Empty set (0.00 sec)
增加
说明:主键列是自动增长,但是在全列插入时需要占位,通常使用0或者 default 或者 null 来占位,插入成功后以实际数据为准
1,全列插入(值得顺序与表中字段的顺序对应)
insert into 表名 values(...)
mysql> insert into students values(0,'一一',13,1.5,'女',1);
Query OK, 1 row affected (0.00 sec)
2,部分列插入(值得顺序与给出的列对应)
insert into 表名(列1,...) values(值1,...)
mysql> insert into students(name) values('而而');
Query OK, 1 row affected (0.00 sec)
上面的语句一次可以向表中插入一行数据,还可以一次性插入多行数据,这样可以减少与数据库的通信
3,全列多行插入(值的顺序与给出的列顺序对应)
insert into 表名 values(...),(...)...;
mysql> insert into classes values(0,'python12'),(0,'python34');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
insert into 表名(列1,...) values(值1,...),(值1,...)...;
mysql> insert into students(name) values('小小'),('欣欣'),('培培');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
修改
update 表名 set 列1=值1,列2=值2... where 条件
mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 一一 | 13 | 1.50 | 女 | 1 |
| 2 | 而而 | 0 | NULL | NULL | 0 |
| 3 | 小小 | 0 | NULL | NULL | 0 |
| 4 | 欣欣 | 0 | NULL | NULL | 0 |
| 5 | 培培 | 0 | NULL | NULL | 0 |
+----+--------+------+--------+--------+--------+
5 rows in set (0.00 sec)
mysql> update students set name="哈哈",age=18 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 哈哈 | 18 | 1.50 | 女 | 1 |
| 2 | 而而 | 0 | NULL | NULL | 0 |
| 3 | 小小 | 0 | NULL | NULL | 0 |
| 4 | 欣欣 | 0 | NULL | NULL | 0 |
| 5 | 培培 | 0 | NULL | NULL | 0 |
+----+--------+------+--------+--------+--------+
5 rows in set (0.00 sec)
删除
delete from 表名 where 条件
mysql> delete from students where id=2;
Query OK, 1 row affected (0.00 sec)
逻辑删除,本质就是修改操作
update students set isdelete=1 where id=1;
备份
运行mysqldump命令
mysqldump –uroot –p 数据库名 > python.sql;
# 按提示输入mysql的密码
恢复
连接mysql,创建新的数据库
连接mysql,退出连接,执行如下命令
mysql -uroot –p 新数据库名 < python.sql
# 根据提示输入mysql密码
mysql查询
1,查询所有字段
mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 哈哈 | 18 | 1.50 | 女 | 1 |
| 3 | 小小 | 0 | NULL | NULL | 0 |
| 4 | 欣欣 | 0 | NULL | NULL | 0 |
| 5 | 培培 | 0 | NULL | NULL | 0 |
+----+--------+------+--------+--------+--------+
4 rows in set (0.00 sec)
2,查询指定字段
mysql> select age from students;
+------+
| age |
+------+
| 18 |
| 0 |
| 0 |
| 0 |
+------+
4 rows in set (0.00 sec)
3,使用as给字段起别名
mysql> select id as ID,name as 姓名,gender as 性别 from students;
+----+--------+--------+
| ID | 姓名 | 性别 |
+----+--------+--------+
| 1 | 哈哈 | 女 |
| 3 | 小小 | NULL |
| 4 | 欣欣 | NULL |
| 5 | 培培 | NULL |
+----+--------+--------+
4 rows in set (0.01 sec)
4,可以通过as给表起别名
-- 如果是单表查询 可以省略表明
mysql> select id,name,age from students;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 哈哈 | 18 |
| 3 | 小小 | 0 |
| 4 | 欣欣 | 0 |
| 5 | 培培 | 0 |
+----+--------+------+
4 rows in set (0.00 sec)
-- 表名.字段名
mysql> select students.id,students.name,students.gender from students;
+----+--------+--------+
| id | name | gender |
+----+--------+--------+
| 1 | 哈哈 | 女 |
| 3 | 小小 | NULL |
| 4 | 欣欣 | NULL |
| 5 | 培培 | NULL |
+----+--------+--------+
4 rows in set (0.00 sec)
-- 可以通过 as 给表起别名
mysql> select s.id,s.name from students as s;
+----+--------+
| id | name |
+----+--------+
| 1 | 哈哈 |
| 3 | 小小 |
| 4 | 欣欣 |
| 5 | 培培 |
+----+--------+
4 rows in set (0.00 sec)
消除重复行
在select 后面列前使用distinst可以消除重复的行
select distinct 列1,... from 表名;
select distinct gender from students;
条件
select * from 表名 where 条件;
mysql> select * from students where id=1;
+----+--------+------+--------+--------+--------+
| id | name | age | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 哈哈 | 18 | 1.50 | 女 | 1 |
+----+--------+------+--------+--------+--------+
1 row in set (0.00 sec)
where 后面支持多种运算符,进行条件的处理
1,比较运算符
2,逻辑运算符
3,模糊查询
4,范围查询
5,空判断
比较运算符
1,等于: =
2,大于: >
3,小于: <
4,大于等于: >=
5,小于等于: <=
6,不等于: != 或 <>
逻辑运算符
1,and
2,or
3,not
模糊查询
1,like
2,%表示任意多个任意字符
3,_表示一个任意字符
范围查询
1,in表示在一个非连续的范围内
2,between … and … 表示在一个连续的范围内
空判断
1,is null 判空
2,is not null 判非空
优先级
1,优先级由高到低的顺序为:小括号,not,比较运算符,逻辑运算符
2,and比or先运算,如果同时出现并希望先算or,需要结合()使用
排序 为了方便查看数据,可以对数据进行排序
为了方便查看数据,可以对数据进行排序
语法:
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
说明:
将行数据按照列1进行排序,如果某些行列1的相同时,则按照列2排序,以此类推
默认按照列值从小到大排列(asc)
asc从小到大排列,即升序单词ascend缩写
desc从大到小排序,即降序 单词descend缩写
聚合函数
为了快速得到统计数据,经常会用到如下5个聚合函数
总数:
count(*)表示计算总行数,括号中写星计算总行数,也可以写列名,比如count(height) 但是写列明不会统计 值为null的情况
最大值 :
max(列)表示求此列的最大值
最小值 :
min(列)表示求此列的最小值
求和 :
sum(列)表示求此列的和
平均值 (average):
avg(列)表示求此列的平均值
分组
group by
group by的含义:将查询结果按照1个或多个字段进行分组,字段值相同的为一组
group by可用于单个字段分组,也可用于多个字段分组
group by + group_concat()
group_concat(字段名)可以作为一个输出字段来使用,
表示分组之后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合
group by + 集合函数
通过group_concat()的启发,我们既然可以统计出每个分组的某字段的值的集合,那么我们也可以通过集合函数来对这个值的集合做一些操作
group by + having
having 条件表达式:用来分组查询后指定一些条件来输出查询结果
having作用和where一样,但having只能用于group by
group by + with rollup
with rollup的作用是:在最后新增一行,来记录当前列里所有记录的总和
分页
语法:(从start开始,获取count条数据)
select * from 表名 limit start,count
第n页的数据
select * from students where is_delete=0 limit (n-1)*m,m
连接查询
mysql支持三种类型的连接查询,分别为:
内连接查询:查询的结果为两个表匹配到的数据
右连接查询:查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用null填充
左连接查询:查询的结果为两个表匹配到的数据,左表特有的数据,对于右表中不存在的数据使用null填充
语法:
select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
子查询
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
主查询
主要查询的对象,第一条 select 语句
主查询和子查询的关系
子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句
子查询分类
标量子查询: 子查询返回的结果是一个数据(一行一列)
列子查询: 返回的结果是一列(一列多行)
行子查询: 返回的结果是一行(一行多列)
子查询中特定关键字使用
in范围
格式:主查询where条件in(列子查询)