常用sql语句整理（MySQL）

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)主键主要是用于其他表的外键关联，以及本记录的修改与删除。(6)default 默认值(7)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;

 优先级由高到低的顺序为：小括号，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.11 主查询（主要查询的对象,第一条 select 语句，我们理解为主查询语句 ）

6.12 子查询（在一个 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.13 事务（原子性(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.14索引（当数据库中数据量很大时，查找数据会变得很慢 ，解决方案索引，索引是一种特殊的文件(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.15 连接查询（当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回 ），连接查询分为三类：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.说明: 以右表为主表