create table students
(
id int unsigned not null auto_increment primary key,
name char(8) not null,
sex char(4) not null,
age tinyint unsigned not null,
tel char(13) null default "-"
);
tinyint的取值范围为 -127到128;加了unsigned(非负)就变成0-255
常用sql
显示表的数据结构:show create table `t1 \G;
插入
insert into students values(NULL, "王刚", "男", 20, "13811371377");
insert into courses_video(`name`,`lesson_id`) select `name`,`lesson_id` from `courses_video` where id=1;
筛选
select * from students where id>3 and age > 5;select * from students where name like binary '%TOM'%; # 筛选name包含TOM的记录,binary:区分大小写
更新
update students set name='tom',age=12 where id=19 ;
update table_1 t1,table_2 t2 set t1.column = t2.column where t1.id = t2.pid;
删除
delete from students where id=11;alter table `students` add column time1 datetime after age,add column time2 decimal(5,2) after age;
修改列: mysql好像不能只操作列名进行修改,需要change的时候也带上属性
将表 tel 列改名为 telphone: alter table students change tel telphone char(13) default "-";
将 name 列的数据类型改为 char(16): alter table students change name name char(16) not null;
删除 birthday ,age列: alter table students drop column birthday,drop column age;
重命名 students 表:alter table students rename class;
删除 workmates 表: drop table workmates;
删除 samp_db 数据库: drop database samp_db;
排序
select * from students where name like '%tom%' order by age asc; (desc 降序,默认是升序)
group by
mysql> select name,count(*) from students group by name;
+-------+----------+
| name | count(*) |
+-------+----------+
| andi | 1 |
| aTOMb | 1 |
| john | 4 |
| TOM | 4 |
+-------+----------+
4 rows in set
mysql> select name,sum(age) as age_count from students group by name; # 计算age的数值和
+-------+-----------+
| name | age_count |
+-------+-----------+
| andi | 3 |
| aTOMb | 3 |
| john | 9 |
| TOM | 10 |
+-------+-----------+
4 rows in set
加入ROLLUP 计算age的总数
mysql> select name,sum(age) as age_count from students group by name with ROLLUP;
+-------+-----------+
| name | age_count |
+-------+-----------+
| andi | 3 |
| aTOMb | 3 |
| john | 9 |
| TOM | 10 |
| NULL | 25 |
+-------+-----------+
5 rows in set
mysql> select coalesce(name,'总数'),sum(age) as age_count from students group by name with rollup;
+-----------------------+-----------+
| coalesce(name,'总数') | age_count |
+-----------------------+-----------+
| andi | 3 |
| aTOMb | 3 |
| john | 9 |
| TOM | 10 |
| 总数 | 25 |
+-----------------------+-----------+
5 rows in set
group_concat
group_concat()会计算哪些行属于同一组,将属于同一组的列显示出来。要返回哪些列,由函数参数(就是字段名)决定。分组必须有个标准,就是根据group by指定的列进行分组。
mysql> select id,name,lesson_id from `courses_video`;
+----+--------------------------------+-----------+
| id | name | lesson_id |
+----+--------------------------------+-----------+
| 1 | 1.1 交互式解释器1 | 2 |
| 2 | 1.2 算法是什么2 | 2 |
| 3 | 1.3 数和表达式3 | 2 |
| 4 | 2.1 序列概述23 | 3 |
| 5 | 2.2 通用的序列操作24 | 3 |
| 7 | 2.2.1 索引24 | 3 |
| 8 | 2.2.2 切片26 | 3 |
| 9 | 2.2.3 序列相加28 | 3 |
| 10 | 3.1 字符串基本操作4 | 4 |
| 11 | 3.2 设置字符串的格式:精简版41 | 4 |
| 12 | 3.3 设置字符串的格式:完整版43 | 4 |
+----+--------------------------------+-----------+
11 rows in set
mysql> select group_concat(name) from `courses_video`;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| group_concat(name) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1.1 交互式解释器1,1.2 算法是什么2,1.3 数和表达式3,2.1 序列概述23,2.2 通用的序列操作24,2.2.1 索引24,2.2.2 切片26,2.2.3 序列相加28,3.1 字符串基本操作4,3.2 设置字符串的格式:精简版41,3.3 设置字符串的格式:完整版43 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql> select group_concat(name) from `courses_video` group by `lesson_id`;
+-----------------------------------------------------------------------------------+
| group_concat(name) |
+-----------------------------------------------------------------------------------+
| 1.1 交互式解释器1,1.2 算法是什么2,1.3 数和表达式3 |
| 2.1 序列概述23,2.2 通用的序列操作24,2.2.1 索引24,2.2.2 切片26,2.2.3 序列相加28 |
| 3.1 字符串基本操作4,3.2 设置字符串的格式:精简版41,3.3 设置字符串的格式:完整版43 |
+-----------------------------------------------------------------------------------+
3 rows in set
三表查询
基于上边的group_concat,进行三表查询
select a.name,course_org_id,b.name,group_concat(c.name) from courses_course a ,organization_courseorg b,organization_teacher c where a.course_org_id=b.id and b.id=c.org_id group by a.id;
![](https://i-blog.csdnimg.cn/blog_migrate/51b5a3792349f1f9a8b6d652cb1b8ed3.png)
外键
用于关联2个表,首先创建2个表,class和std3,std3的class_id 用于关联class的id
mysql> create table class(
-> id int not null primary key,
-> name char(16));
Query OK, 0 rows affected (0.02 sec)
CREATE TABLE `std3` (
`id` int(11) NOT NULL auto_increment,
`name` char(16) NOT NULL,
`class_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_class_key` (`class_id`), # 一定要写int 因为关联的外键是int类型
CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
);
创建好表后,外键主要有2个特性
1.class表的id只有1的话,那么std3的表的class_id 只能是1。class的id有2了,那么std3 的class_id也可以为2,这也就是外键约束。(如果把被关联和关联的记录记为老大和小弟。那么这个特性可以记为:老大还没有呢?小弟你怎么可以有)
mysql> insert into std3(class_id) values(0) ;
1452 - Cannot add or update a child row: a foreign key constraint fails (`testlv`.`std3`, CONSTRAINT `fk_cls_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
mysql> insert into class(id) values(0) ;
Query OK, 1 row affected
mysql> insert into std3(class_id) values(0) ;
Query OK, 1 row affected
2.如果class表有被关联的数据,那么是不能删除class表的这条记录的。(可以记为:老大你不能跑路,你走啦,你的小弟咋办)
mysql> delete from class where id=0 ;
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`testlv`.`std3`, CONSTRAINT `fk_cls_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
mysql> delete from std3 where class_id=0 ;
Query OK, 1 row affected
mysql> delete from class where id=0 ;
Query OK, 1 row affected
null 注意
在进行where语句筛选时为了处理这种情况,MySQL提供了三大运算符:
IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。
<=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。
比如:select * from students where weight <=> height; 就是找出weight和height都为null的值
在MySQL中,NULL值与任何其它值的比较(即使是NULL)永远返回false,即 NULL = NULL 返回false 。
MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。
inner/left/right join 的应用
直接看示例,一目了然,跟pandas的join原理一致
创建2个单列表
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 6 |
| 10 |
+----+
4 rows in set
mysql> select * from t2 ;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
| 7 |
+----+
5 rows in set
inner join 其实就是取交集
mysql> select t1.*,t2.* from t1,t2 where t1.id = t2.id; # 如果想只想取t1的id select t1.id,t2.* from t1,t2 where t1.id=t2.id;
+----+----+
| id | id |
+----+----+
| 1 | 1 |
| 2 | 2 |
| 6 | 6 |
+----+----+
3 rows in set
mysql> select * from t1 inner join t2 on t1.id = t2.id;
+----+----+
| id | id |
+----+----+
| 1 | 1 |
| 2 | 2 |
| 6 | 6 |
+----+----+
3 rows in set
left join
mysql> select * from t1 left join t2 on t1.id = t2.id;
+----+------+
| id | id |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 6 | 6 |
| 10 | NULL |
+----+------+
4 rows in set
right join
mysql> select * from t1 right join t2 on t1.id = t2.id;
+------+----+
| id | id |
+------+----+
| 1 | 1 |
| 2 | 2 |
| NULL | 3 |
| 6 | 6 |
| NULL | 7 |
+------+----+
5 rows in set
full join 下图
select * from t1 FULL JOIN t2 on t1.id = t2.id;
+------+------+
| id | id |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 6 | 6 |
| 10 | NULL |
| NULL | 3 |
| NULL | 7 |
+------+------+
6 rows in set
但是mysql可能不支持full join,但是可以通过以下办法实现相同的功能
mysql> select * from t1 left join t2 on t1.id=t2.id union select * from t1 right join t2 on t1.id=t2.id;
+------+------+
| id | id |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 6 | 6 |
| 10 | NULL |
| NULL | 3 |
| NULL | 7 |
+------+------+
6 rows in set
事务:
MySQL 事务主要用于处理操作量大,复杂度高的数据(如果你开始一个事物,修改了字段的属性:比如删除主键操作。那么,接下来的操作即使是数据的增删改,用rollback也不会回滚了,已经写到表里了)。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!
- 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
- 事务用来管理insert,update,delete语句
一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
- 1、事务的原子性:一组事务,要么成功;要么撤回。
- 2、稳定性 : 有非法数据(外键约束之类),事务撤回。
- 3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
- 4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
mysql> select * from t2 ;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
| 7 |
+----+
5 rows in set
-> begin; # 开始事物
Query OK, 0 rows affected
-> insert into t2(id) values(20);
Query OK, 1 row affected
mysql> insert into t2(id) values(21);
Query OK, 1 row affected
mysql> select * from t2 ;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
| 7 |
| 20 |
| 21 |
+----+
7 rows in set
mysql> rollback ; # 回滚,就不会写入
Query OK, 0 rows affected
-> select * from t2 ;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
| 7 |
+----+
5 rows in set
当然最后,如果你确认没问题就可以用commit 进行提交
导出数据库
× 在一个mysql库里备份数据库
1. 先进入mysql创建一个 edu_bak的数据库
2. 进入终端,将educate的数据库复制到edu_bak的数据库中
[lh@izbp177egh3y2vdv1jodhsz ~]$mysqldump educate -u root -p123456 --add-drop-table | mysql edu_bak -u root -p123456
× 将数据库导出到sql文件
[lh@izbp177egh3y2vdv1jodhsz ~]$ mysqldump -u root -p123456 educate > educate.sql
然后将educate.sql备份到新的数据库(test)了
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> source ~/educate.sql
MySQL中MyISAM与InnoDB区别及选择
InnoDB:
支持事务处理等不加锁读取
支持外键
支持行锁
不支持FULLTEXT类型的索引
不保存表的具体行数,扫描表来计算有多少行
DELETE 表时,是一行一行的删除
InnoDB 把数据和索引存放在表空间里面
跨平台可直接拷贝使用
InnoDB中必须包含AUTO_INCREMENT类型字段的索引
表格很难被压缩
MyISAM:
不支持事务,回滚将造成不完全回滚,不具有原子性
不支持外键
不支持外键
支持全文搜索
保存表的具体行数,不带where时,直接返回保存的行数
DELETE 表时,先drop表,然后重建表
MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸
跨平台很难直接拷贝
MyISAM中可以使AUTO_INCREMENT类型字段建立联合索引
表格可以被压缩
选择:
因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多,写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。
如果系统读少,写多的时候,尤其是并发写入高的时候。InnoDB就是首选了。
两种类型都有自己优缺点,选择那个完全要看自己的实际类弄。