- 数据库的基本操作
创建数据库:create databases 数据库的名称
删除数据库:drop databases 数据库的名称 - 数据表的基本操作
(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。一个表可以定义多个唯一约束,但只能有一个主键约束。 - 修改表的结构
(1)修改表名:alter table rename [to] ;
(2)修改字段的关键字:modify 可以修改字段的类型,change 可以修改字段的类型和字段名称,add添加字段,drop 删除字段,engine=修改表的存储引擎。在语句最后添加first表示修改后该字段放在第一列,也可跟after<字段名>表示放在指定字段的后面。默认新添加的字段在最后一列。 - 数据查询
(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”; - 插入、删除和更新
(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 … 不加条件时删除表中的所有记录。 - 索引
(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)
建立索引
mysql> create index index_name on stu(age);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次查询
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)创建视图:
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 来恢复。 - 优化
优化查询:使用索引查询时要注意,用like查询时“%”不能出现在字符串中的第一个,否则,不会使用索引查询。多列查询时,列中的项为组合索引的最左前项,组合所以才会被使用。使用or查询语句时前后两个列都有索引时索引才会被使用。
子查询的内层查询会产生临时表,外层查询结束后临时表被销毁。因此子查询效率低,可以用连接查询来代替子查询。