|
1.连接数据库：
? ? ? ? # 第一种方式 ? ? ? ?mysql -u用户名 -p密码 ? ? ? ?例如：mysql -uroot -pmysql
? ? ? ? # 第二种方式 ? ? ? ?mysql -u用户名 -p
? ? ? ? # 区别：第二种链接方式不直接携带密码，需要操作者回车之后输入，不用把密码暴露在所有人面前，相对安全
2.退出数据库：
? ? ? ? exit 或 quti 或 ctrl + d
3.数据库的增删改查:
? ? ? ? 1>创建数据库：
# 建议使用此种格式创建数据库
create database 数据库名 charset=utf8;
? ? ? ? 2>查看创建数据库的语句：
show create database 数据库名;
? ? ? ? 3>使用数据库：
use 数据库的名字;
? ? ? ? 4>删除数据库：
drop database 数据库名;
4.表的增删改查:
? ? ? ? 1>查看当前数据库中的所有数据表：
show tables;
? ? ? ? 2>创建表:
create table 数据表名(具体字段);
(1)int unsigned 无符号整形
(2)auto_increment 表示自动增长
(3)not null 表示不能为空
(4)primary key 表示主键 数据库主键，指的是一个列或多列的组合，
(5)其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。
(6)主键主要是用于其他表的外键关联，以及本记录的修改与删除。
(7)default 默认值
(8)create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
? ? ? ? 3>查看刚刚所创建的表：?
show create table 表名字;
? ? ? ? 4>删除表：
drop table 表名;
? ? ? ? 5>修改表:
(1)添加字段：alter table 表名 add 列名 类型;
? ? # 添加chongwu（宠物）字段
? ? ?alter table classes add chongwu varchar(20) default"蛇";
?(2)修改字段：不重命名版: ?alter table 表名 modify 列名 类型及约束;
? ? ?alter table classes modify mascot varchar(30) default"海娃";
(3)修改字段：重命名版:alter table 表名 change 原名 新名 类型及约束;
? ? ?alter table classes change chongwu mascot varchar(20) default"兰博基尼";
?(4)删除字段： alter table 表名 drop 列名;
? ? ?alter table classes drop mascot;
(5)数据表重命名:alter table 原表名 rename to 新表明；
?(6)外键约束：
?对于已经存在的字段添加外键约束：
? ? alter table students add foreign key(cls_id) references classes(id);
?添加数据表是增加外键约束：
?-- 创建学校表
? create table school(id int not null primary key auto_increment,name varchar(10));
?-- 创建老师表
? create table teacher(id int not null primary key auto_increment,name ?
?
varchar(10),s_id int not null,foreign key(s_id) references school(id));
?删除外键约束： ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? -- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句 ? ? ? ? ? ? ? ? ? ? ? ? ? ?来获取名称：show create table teacher;
? -- 获取名称之后就可以根据名称来删除外键约束：alter table teacher drop foreign key 外键名;
5.表中数据的增删改查：
? ? ? ? 1>数据的增加：
-- 1. 全列插入：值的顺序与表结构字段的顺序完全一一对应
insert into 表名 values (...)
例:
insert into students values(0, 'xx', default, default, '男');
-- 2. 部分列插入：值的顺序与给出的列顺序对应
insert into 表名 (列1,...) values(值1,...)
例:
insert into students(name, age) values('王二小', 15);
-- 3. 全列多行插入
insert into 表名 values(...),(...)...;
例:
insert into students values(0, '张飞', 55, 1.75, '男'),(0, '关羽', 58, 1.85, '男');
-- 4. 部分列多行插入
insert into 表名(列1,...) values(值1,...),(值1,...)...;
例：
insert into students(name, height) values('刘备', 1.75),('曹操', 1.6);
? ? ? ? ? ? 2>数据的删除：? ? ? ? ? ? ? ? ? ?
(1)物理删除： ??
? ? --delete from 表名 where 条件
(2)逻辑删除:
? ? -- 添加删除表示字段，0表示未删除 1表示删除
? ? --alter table students add isdelete bit default 0;
-- 逻辑删除数据
? ? --update students set isdelete = 1 where id = 8；
? ? ? ? ? ? ? ? 3> 数据的修改： ? ??? ? ? ? ? ? ? ? ? ? ? ?
update 表名 set 列1=值1,列2=值2... where 条件；?
update students set age = 18, gender = '女' where id = 6;
? ? ? ? ? ? ? ? 4>数据的查询：? ? ? ? ? ? ? ? ? ? ?
? -- 1. 查询所有列：select * from 表名;
? -- 2. 查询指定列：select 列1,列2,... from 表名;
6.表中数据查询：
使用where子句对表中的数据筛选，结果为true的行会出现在结果集中,where后面支持多种运算符，进行条件的处理，比较运算符，逻辑运算符，模糊查询，范围查询，空判断
6.1 比较运算符：
例：查询编号大于3的学生
select * from students where id > 3;
?
例：查询编号不大于4的学生
select * from students where id <= 4;
?
例：查询姓名不是“黄蓉”的学生
select * from students where name != '黄蓉';
select * from students where name <> '黄蓉';
?
例：查询编号小于3的学生
select * from students where id < 3;
?
例：查询编号不小于4的学生
select * from students where id >= 4;
?
例：查询姓名是“黄蓉”的学生
select * from students where name = '黄蓉';
6.2 逻辑运算符：（and,not,or）?
例：查询编号大于3的女同学
select * from students where id > 3 and gender=2;
?
例：查询编号大于3或编号小于2的同学
select * from students where id > 3 or id<2;
?
例：查询性别不为空的同学
select * from students where gender is not null;
6.3 模糊查询：（like，%表示任意多个任意字符，_表示一个任意字符 ）
例：查询姓李的学生
select * from students where name like '李%';
?
例：查询姓**李**并且名是一个字的学生
select * from students where name like '李_';
?
例：查询姓李或叫磊的学生
select * from students where name like "李%" or name like "%磊";
?
6.4 范围查询：（in表示在一个非连续的范围内 ，between ... and ...表示在一个连续的范围内 ）
例：查询编号是1或3或5的学生
select * from students where id in(1,3,5);
?
例：查询编号为1至4的学生
select * from students where id between 1 and 4;
select * from students where id>=1 and id<=4;
?
例：查询编号是1至4的女生
select * from students where id between 1 and 4 and gender="女";
select * from students where id >=1 and id <=4 and gender=2;
6.5 是否为空：（注意: null与''是不同的 ，为空: is null ）
例：查询没有填写身高的学生
select * from students where height is null;
?
例：查询填写了身高的学生
select * from students where height is not null;
?
例：查询填写了身高的男生
select * from students where height is not null and gender=1;
6.6 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符，and比or先运算，如果同时出现并希望先算or，需要结合()使用
select * from students where height >170 or age > 20 and gender="女";
select * from students where (height >170 or age > 20) and gender="女";
?6.7 排序：将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推，默认按照列值从小到大排列（asc），asc从小到大排列，即升序，desc从大到小排序，即降序
例：查询未删除男生信息，按学号降序
select * from students where gender=1 and isdelete=0 order by id desc;
?
例：查询未删除学生信息，按名称升序
select * from students where isdelete=0 order by name;
?
例：显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时 按照身高从高-->矮排序
select * from students ?order by age desc,height desc;
?6.8 分页：
从start开始，获取count条数据
例：查询前3行男生信息
select * from students where gender=1 limit 0,3;
获取第你页数据，每页m条
select * from students where is_delete=0 limit (n-1)*m,m;
6.9 分组：（group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组 ，
group by可用于单个字段分组，也可用于多个字段分组 ）
select gender from students group by gender;
group_concat(字段名)可以作为一个输出字段来使用， 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合：
?
select gender as "性别", group_concat(name) as "学生" from students group by gender;
6.10 过滤 ：（having语句通常与group by语句联合使用，用来过滤由group by语句返回的记录集，having语句的存在弥补了where关键字不能与聚合函数联合使用的不足 ） ? ? ? ? ?
select group_concat(id,name) ?as "姓名", height as "身高(cm)"
from students ?where id >=1 group by height having height is not null;
6.11 聚合函数（count、max、min，sum，avg）
例：查询学生总数
select count(*) from students;
例：查询女生的编号最大值
select max(id) from students where gender=2;
例：查询未删除的学生最小编号
select min(id) from students where is_delete=0;
例：查询男生的总年龄
select sum(age) from students where gender=1;
例：查询未删除女生的编号平均值
select avg(id) from students where is_delete=0 and gender=2;
6.12 起别名（as）
select gender as "性别",max(age) as "最大",min(age) as "最小",sum(age) as "总年龄 ",
avg(age) as "平均年龄" ?from students where gender=1;
6.13?主查询（主要查询的对象,第一条 select 语句，我们理解为主查询语句 ）
6.14?子查询（在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，标量子查询: 子查询返回的结果是一个数据(一行一列) ，列子查询: 返回的结果是一列(一列多行) ，行子查询: 返回的结果是一行(一行多列) ?）
标量子查询：查询班级学生的平均身高
select * from students where age > (select avg(age) from students);
列级子查询：查询还有学生在班的所有班级名字
select name from classes where id in (select cls_id from students);
行级子查询：查找班级年龄最大,身高最高的学生
select * from students where (height,age) = (select max(height),max(age) from students);
子查询中特定关键字使用 ：主查询 where 条件 in (列子查询) ?
6.15?事务（原子性(Atomicity)，一致性(Consistency)，隔离性(Isolation)，持久性(Durability)）
?表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
-- 选择数据库
use jing_dong;
-- 查看goods表
show create table goods;
开启事务，命令如下：开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
begin;或者start transaction;
提交事务，命令如下：将缓存中的数据变更维护到物理表中
commit;
?事务提交流程演示： 为了演示效果，需要打开两个终端窗口，使用同一个数据库，操作同一张表（用到之前的jing_dong数据，可以回到mysql第3天中查看）
step1：连接- 终端1：查询商品分类信息
select * from goods_cates;
?
step2：增加数据- 终端2：开启事务，插入数据
begin;
insert into goods_cates(name) values('小霸王游戏机');
- 终端2：查询数据，此时有新增的数据
select * from goods_cates;
?
step3：查询
- 终端1：查询数据，发现并没有新增的数据
select * from goods_cates;
?
step4：提交
- 终端2：完成提交
commit;
?
step5：查询
- 终端1：查询，发现有新增的数据
select * from goods_cates;
事物的回滚：回滚事务，放弃缓存中变更的数据（rollback;）事务回滚流程
step1：连接
- 终端1
select * from goods_cates;
?
step2：增加数据
- 终端2：开启事务，插入数据
begin;
insert into goods_cates(name) values('小霸王游戏机');
- 终端2：查询数据，此时有新增的数据
select * from goods_cates;
?
step3：查询
- 终端1：查询数据，发现并没有新增的数据
select * from goods_cates;
?
step4：回滚
- 终端2：完成回滚
rollback;
?
step5：查询
- 终端1：查询数据，发现没有新增的数据
select * from goods_cates;
6.16索引（当数据库中数据量很大时，查找数据会变得很慢 ，解决方案索引，索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度 ）?
索引的使用：
创建索引
- 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
- 字段类型如果不是字符串，可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))
?
删除索引：
drop index 索引名称 on 表名;
?
查看索引
show index from 表名;
百万级索引实战对比：
创建测试表testindex
show index from 表名;
?
使用python程序（ipython也可以）通过pymsql模块 向表中加入十万条数据
from pymysql import connect
?
def main():
? ? # 创建Connection连接
? ? conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
? ? # 获得Cursor对象
? ? cursor = conn.cursor()
? ? # 插入10万次数据
? ? for i in range(1000000):
? ? ? ? cursor.execute("insert into test_index values('ha-%d')" % i)
? ? # 提交数据
? ? conn.commit()
?
if __name__ == "__main__":
? ? main()
?
查询：开启运行时间监测：
set profiling=1;
查找第1万条数据ha-99999
select * from test_index where title='ha-99999';
查看执行的时间：
show profiles;
为表title_index的title列创建索引：
create index title_index on test_index(title(10));
执行查询语句：
select * from test_index where title='ha-99999';
再次查看执行的时间
show profiles;
6.17?连接查询（当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回 ），连接查询分为三类：1>内连接查询：查询的结果为两个表匹配到的数据,2>右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充 ,3>左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充
例：** 使用内连接查询班级表与学生表(交叉)
此处使用了as为表起别名，目的是编写简单
select s.id as "编号",s.name as "姓名",s.age as "年龄",s.gender as "性别", c.name as "班级" from students as s inner join class as c on s.cid=c.id;
?
例：** 使用左连接查询班级表与学生表(学生表)
select s.id as "编号",s.name as "姓名",s.age as "年龄",s.gender as "性别",c.name as "班级" from students as s left join class as c on c.id=s.cid order by c.id,s.id;
?
例：** 使用右连接查询班级表与学生表(班级表)
select s.id as "编号",s.name as "姓名",s.age as "年龄",s.gender as "性别",c.name as "班级" from students as s right join class as c on c.id=s.cid order by c.id,s.id;
外连接查询：左外连接
# 左外连接:??
1.左外连接语法:
select 字段 from 表1 left join 表2 on 连接条件;?
2.作用: 左表作为主表(表1), 根据主表的数据进行查询, 当主表在从表中找不到匹配项则使用null填充
3.语句示例:
SELECT e.name, e.age, d.name FROM employ e LEFT JOIN department d ON e.`dp_id`=d.id;
外连接查询：右外连接
# 1.5 右外连接
# 1.右外连接语法:
?? ?select 字段 from 表1 right join 表2 on 连接条件;?
# 2.作用:?
右表作为主表(表2), 根据右表的数据进行查询, 当右表在左表中找不到匹配项则使用null填充
# 3.语句示例:
? ? SELECT e.name, e.age, d.name FROM employ e RIGHT JOIN department d ON e.`dp_id`=d.id;
# 4.说明: 以右表为主表
|