MySql的基本操作（建库、建表、增删改查、索引、视图、备份）_mysql 建库,建表,视图查表

1. 数据库的基本操作
   创建数据库：create databases 数据库的名称
   删除数据库：drop databases 数据库的名称
2. 数据表的基本操作
   （1）添加主键约束：在当前字段后面加primary key，或者在所有字段后加primary key（字段名）。此时字段名可以取多个，取多个时表示联合主键。
   （2）添加外键约束：从表中的外键依赖主表中的主键。外键可以为空，如果不为空则一定要和主表中的一个主键的值相对应。建立从表时在所有字段后面加[constraint <外键名>] foreign key（从表中的要做外键的字段名） references 主表名（主键名（被关联的字段名））。删除主表时必须先删除外键关联，alter table <表名> drop foreign key <约束名>，可以通过show create table \G 来查看约束名（\G表示格式化输出）
   （3）非空约束和唯一约束：非空约束在当前字段后面加not null，保证字段的值不为空；唯一约束在字段后面加unique，保证字段的值没有重复，可以有null。一个表可以定义多个唯一约束，但只能有一个主键约束。
3. 修改表的结构
   （1）修改表名：alter table rename [to] ;
   （2）修改字段的关键字：modify 可以修改字段的类型，change 可以修改字段的类型和字段名称，add添加字段，drop 删除字段，engine=修改表的存储引擎。在语句最后添加first表示修改后该字段放在第一列，也可跟after<字段名>表示放在指定字段的后面。默认新添加的字段在最后一列。
4. 数据查询
   （1）带in关键字查询：select * from stu_info where sid in(111,222)，在学生信息表中查询学号为111和222的学生的所有信息。
   （2）between…and…：表示范围查询。
   （3）带Iike的查询：“%”可以匹配任意长度的字符，“_”只能匹配一个字符。
   （4）去掉查询结果中的重复值：在字段前面加distinct。
   （5）查询结果排序：默认升序排列，加order by <字段名> desc 是降序排列。可以添加多个字段名
   （6）分组查询：group by <字段名> [having <条件表达式>]，分组后可以对每一组统计个数，平均值，总数等
   （7）limit限制查询结果数量：limit a[,b]; 没有b时表示从第一条向后显示a条数据；有b时表示从a开始，向后显示b条数据。
   （8）连接查询：多表查询就是连接查询。
   内连接：select * from <表1>inner join <表2> on <约束条件> 等价于select * from <表1>,<表2> where <约束条件>
   左连接：包括左表中的所有记录和右表中连接字段相等的记录，右表中没有对应值的为null
   右连接：包括右表中的所有记录和左表中连接字段相等的记录，左表中没有对应值的为null
   例：学生和老师
   学生可以自学，也可以由老师教；老师可以教学，也可以不教学。则某些学生对应老师那一栏的值为null，某些老师对应学生那一栏的值为null，内连接查询出的结果是所有有对应关系的老师和学生，学生和老师都不为null。
   （9）子查询：关键字any、some、all、in 可以放在子查询结果前，组成条件语句的一部分。select name from stu where age>=all(select age from stu_info) 在stu表中查询年龄最大的学生的姓名。
   （10）合并查询：用union [all]连接两个查询语句，查询的字段要相等。有all是不去重复值。
   （11）正则表达式查询：regexp “^b"表示以b开头的字符串，“a$”以a结尾，“.”匹配任意单个字符，“*”匹配0个或多个前面的字符，“+”匹配1个或多个前面的字符。”[0-9]5"匹配5个数字。select * from stu where name regexp “^y”；
5. 插入、删除和更新
   （1）插入：insert into table_name() values();
   insert into table_name values(); values中的值必须和表的结构中字段相对应
   insert into table_name values(),(),(); 一次插入多条数据。效率高于一次插入一条数据
   insert into table_name(字段1,字段2) select 字段1,字段2 from table_name1 where … 将查询的结果插入到表中。
   （2）更新：update table_name set 字段=new_value where … 更新符合条件的字段的值。不加where是，更新该字段的所有值。
   （3）删除：delete from table_name where … 不加条件时删除表中的所有记录。
6. 索引
   （1）索引创建的方式：
   建表时：在最后一行加上索引的类型、建立索引的字段名。字段名可以为多个，多个表示组合索引。
   unique index index_name(id) 表示在id上创建了名叫index_name的唯一索引。
   表已建立完成：alter table table_name add unique index index_name(id) 在id上建立名叫index_name的唯一索引。
   create unique index index_name on table_name(字段)
   （2）查看表中的索引：
   show index from table_name\G
   （3）删除索引：
   alter table table_name drop index index_name;
   drop index index_name on table_name;
   （4）创建索引后查询速度明显加快。在没有索引的情况下查询数据会一条一条查询，有索引会直接查到。可以用explain select * from stu where …
   未建立索引时的查询，rows=9表示查询了9次

mysql> explain select * from stu where age=18\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stu
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

建立索引

mysql> create index index_name on stu(age);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3

再次查询

mysql> explain select * from stu where age=18\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stu
   partitions: NULL
         type: ref
possible_keys: index_name
          key: index_name
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.11 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

7. 视图
   （1）创建视图：
   create view view_name1(字段1,字段2,字段3) as select 字段1,字段2,字段3 from table_name where … view_name 后面也可以不加字段和括号。对视图的操作可以近似为对表的操作。
   （2）查看视图：desc view_name
   show table status like ‘视图名’\G
   show create view view_name\G
   （3）修改视图：create or replace view view_name as select 字段…from table_name where …
   alter view view_name as select …
   （4）更新视图：update view_name set … 更新完后数据表中的内容也被更新。
   （5）删除视图：drop view view_name
   8.数据库的备份与恢复：
   （1）备份数据库：mysqldump -u username -p password database>e:\abc.sql ，也可以备份数据库中的数据表，在库名后面加上表明即可。备份多个数据库时，在数据库名前加“–databases”参数
   （2）恢复数据库：mysql -u username -p password database<e:\abc.sql 数据库database需提前建好。
   建好数据后 也可用 source e:\abc.sql 来恢复。
8. 优化
   优化查询：使用索引查询时要注意，用like查询时“%”不能出现在字符串中的第一个，否则，不会使用索引查询。多列查询时，列中的项为组合索引的最左前项，组合所以才会被使用。使用or查询语句时前后两个列都有索引时索引才会被使用。
   子查询的内层查询会产生临时表，外层查询结束后临时表被销毁。因此子查询效率低，可以用连接查询来代替子查询。