数据库mysql的基本操作

操作存在的数据表

修改表名：

alter table 表名 rename 新表名

删除表：

drop table 表名			（不会删除原表中的自增字段的偏移量，将删除表的结构被依赖的约束(constrain),								触发器(trigger),索引(index);）

truncate table 表名		（会删除原表中的自增字段的偏移量，只删除数据不删除表的结构）
delete from 表名 还可以加条件  只删除数据不删除表的结构

修改表字段

alter table 表名 change 旧字段名 新字段名 类型 约束 

新增字段名

alter table 表名 add 新字段名 类型 约束 after 原表中的某个字段

单表操作

# 最简单的select
    # select * from 表;
    # select 字段,... from 表;
    
# 重命名字段
    # select 字段 as 新名字,... from 表;
    # select 字段 新名字,... from 表;
    
# 去重
    # select distinct 字段 from 表;
    # select distinct age,sex from employee;多个字段去重是要都不同才会去重，否则保留
    
# 使用函数
    # concat(多个字段名，会拼接在一起显示)
    # concat_ws("拼接符"，多个字段名)
    
# 四则运算的
    #  select emp_name,salary*12 from employee; 乘法
    #  select emp_name,salary*12 as annual_salary from employee;
    
# 使用判断逻辑
    # case when语句 相当于 if条件判断句

# where 筛选所有符合条件的行
    # 比较运算符
        # > < >= <= <> !=
    # 范围
        # between 10000 and 20000 要1w-2w之间的
        # in (10000,20000)   只要10000或者20000的
    # 模糊匹配
        # like
            # % 通配符 表示任意长度的任意内容
            # _ 通配符 一个字符长度的任意内容
        # regexp
            # '^a'
            # 'g$'
    # 逻辑运算
        # not\and\or

# 查看岗位描述不为NULL的员工信息
    # is
    # select * from employee where post_comment is not null;
# 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
    # select emp_name, age, salary
    # from employee wherepost = 'teacher' and salary not in(10000,9000,30000)
# 查看岗位是teacher且名字是jin开头的员工姓名、年薪
    #  select emp_name,salary*12 from employee where post = 'teacher' and emp_name like 'jin%';

# 分组 group by 根据谁分组,可以求这个组的总人数,最大值,最小值,平均值,求和 但是这个求出来的值只是和分组字段对应
    # 并不和其他任何字段对应,这个时候查出来的所有其他字段都不生效.
# 聚合函数
    # count 求个数
    # max  求最大值
    # min  求最小值
    # sum  求和
    # avg  求平均

    # SELECT post,emp_name FROM employee GROUP BY post;
    # SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;

# having 过滤语句
    # 在having条件中可以使用聚合函数,在where中不行
    # 适合去筛选符合条件的某一组数据,而不是某一行数据
    # 先分组再过滤 : 求平均薪资大于xx的部门,求人数大于xx的性别,求大于xx人的年龄段
# 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
# group by post having count(id) < 2;

# 排序 order by
    # 默认是升序  asc
    # 降序  desc
    # order by age ,salary desc
        # 优先根据age从小到大排,在age相同的情况下,再根据薪资从大到小排

# limit m,n
    # 从m+1项开始,取n项
    # 如果不写m,m默认为0
    # limit n offset m（不常用）


#条件
in(数据1，数据2……)
between a and b （在a,b数据之间；包含a,b数据）
not in(数据1，数据2……)

多表操作

# 所谓连表
    # 总是在连接的时候创建一张大表,里面存放的是两张表的笛卡尔积
    # 再根据条件进行筛选就可以了

# 表与表之间的连接方式

    # 内连接 inner join ... on ...
    
        select * from 表1,表2 where 条件;(了解)
        select * from 表1 inner join 表2  on 条件（自己需要把两张表什么字段连在一起，不显示不符合条件的数据）
        select * from department inner join employee on department.id =employee.dep_id;
        select * from department as t1 inner join employee as t2 on t1.id = t2.dep_id;
        
    # 外连接
    
        # 左外连接 left join ... on ...（以左表数据为准，左表数据都显示）
        select * from 表1 left join 表2 on 条件
         select * from department as t1 left join employee as t2 on t1.id = t2.dep_id;
         
        # 右外连接 right join ... on ...（以右表数据为准，右表数据都显示）
        select * from 表1 right join 表2 on 条件
        
        select * from department as t1 right join employee as t2 on t1.id = t2.dep_id
        
        # 全外连接 full join（连表数据都显示，mysql不支持这种写法，非要用的话就用以下方式解决，通过左右链接后，在其中间加一个union）
        select * from department as t1 left join employee as t2 on t1.id = t2.dep_id
        union
        select * from department as t1 right join employee as t2 on t1.id = t2.dep_id;

索引操作

#创建索引
create index 索引名 on 表名（某个字段）

#删除索引
drop index 索引名 on 表名

PS:索引的创建比较耗时和占空间

正确使用索引

    # 从搭建数据库的角度上来描述问题
    # 建表的角度上
        # 1.合理安排表关系
        # 2.尽量把固定长度的字段放在前面
        # 3.尽量使用char代替varchar
        # 4.分表: 水平分,垂直分
    # 使用sql语句的时候
        # 1.尽量用where来约束数据范围到一个比较小的程度,比如说分页的时候
        # 2.尽量使用连表查询而不是子查询
        # 3.删除数据或者修改数据的时候尽量要用主键作为条件
        
        # 4.合理的创建和使用索引
            # 1.查询的条件字段不是索引字段
                # 对哪一个字段创建了索引,就用这个字段做条件查询
            # 2.在创建索引的时候应该对区分度比较大的列进行创建
                # 1/10以下的重复率比较适合创建索引
            # 3.范围
                # 范围越大越慢
                # 范围越小越快
                # like 'a%'  快
                # like '%a'  慢
            # 4.条件列参与计算/使用函数
            # 5.and和or
                # id name
                # select * from s1 where id = 1800000 and name = 'eva';
                # select count(*) from s1 where id = 1800000 or name = 'eva';
                # 多个条件的组合,如果使用and连接
                    # 其中一列含有索引,都可以加快查找速度
                # 如果使用or连接
                    # 必须所有的列都含有索引,才能加快查找速度，若其中某一个没有索引则都不使用，所以慢
            # 6.联合索引 : 最左前缀原则(必须带着最左边的列做条件,当最左边的索引字段出现范围开始，整条索引失效，就会变慢)
                # create index 索引名 on 表名(字段1，字段2，字段3，……)
                #字段1在where后面查询时需要写在第一栏，还不能出现范围判断例如<、>等，索引才会生效
                # select * from s1 where id = 1800000 and name = 'eva' and email = 'eva1800000@oldboy';
                # select * from s1 where id = 1800000 and name = 'eva';
                # select * from s1 where id = 1800000 and email = 'eva1800000@oldboy';
                # select * from s1 where id = 1800000;
                # select * from s1 where name = 'eva' and email = 'eva1800000@oldboy';
                # (email,id,name)
                # select * from s1 where id >10000 and email = 'eva1800000@oldboy';
            # 7.条件中写出来的数据类型必须和定义的数据类型一致
                # select * from biao where name = 666   # 不一致
            # 8.select的字段应该包含order by的字段
                # select name,age from 表 order by age;  # 比较好
                # select name from 表 order by age;  # 比较差

数据库的备份

# 表和数据的备份
    # 备份数据 在cmd命令行直接执行
    # mysqldump -u加上用户名 -p加上用户密码 -h需要备份的数据所在ip地址 数据库 数据库中需要备份的数据表 > 到指定的位置保存成.sql文件
    #若不指定 数据库中需要备份的数据表  则会把该数据库中的所有表信息都备份出来
    # mysqldump -uroot -p123 -h127.0.0.1 homework work > D:\python_22\day42\tmp.sql

    # 恢复数据 在mysql中执行命令
    # 切换到一个要备份的数据库中： use 要备份到某个数据库名
    # source D:\python_22\day42\tmp.sql

# 备份库
    # 备份库 在cmd命令行直接执行
    #会把该数据库中的所有数据都备份出来，表结构数据等
    # mysqldump -uroot -p123 --databases homework > D:\python_22\day42\tmp2.sql
    
    # 恢复数据 在mysql中执行命令
    # source D:\python_22\day42\tmp2.sql