MySQL操作语法简介
- 数据库
- 创建数据库(使用 help create database; 查看创建数据库语法规则)(使用show character set;查看支持的字符集)(使用show collation;查看支持的排序规则)
create database|schema [if not exists] db_name [character set=utf8] [collate=utf8_general_ci]
- 修改数据库(使用 help alter database; 查看修改数据库的语法规则)
alter database|schema bd_name [character set=utf8] [collate=utf8_general_ci]
- 删除数据库(使用 help drop database; 查看删除数据库的语法规则)
drop database|schema [if exists] db_name
- 创建数据库(使用 help create database; 查看创建数据库语法规则)(使用show character set;查看支持的字符集)(使用show collation;查看支持的排序规则)
- 表(使用show table status; 可以查看表状态)
- 创建表(使用 help create table; 查看创建表的语法规则)(键也称作约束,可用作索引,属于特殊的索引,都是B+Tree结构)
- 直接定义一张空表
create table [if not exists] table_name (col_name col_defination,……) [table_options] [partition_options]
- 直接定义一张空表
- 创建表(使用 help create table; 查看创建表的语法规则)(键也称作约束,可用作索引,属于特殊的索引,都是B+Tree结构)
create table tb1(id int unsigned not null auto_increment,name char(20) ,age tinyint,primary key(id),unique key(id,name),index(age)) engine=InnoDB character set=utf8 collate=utf8_general_ci
* 从其他表查询数据,并以之创建新表 ``create table [if not exists] table_name (col_name col_defination,……) [table_options] [partition_options] query_expression``
msyql
create table tb2 select * from tb1 where id < 3;
* 以其他表为模板创建一个新表 ``create table [if not exists] table_name like old_table_name``
mysql
create table tb3 like tb1;
```
- 修改表(使用 help alter table; 查看修改表语法规则)
alter table table_name [alter_specification [, alter_specification] ...]
- add [columns] col_name col_definition
- add index|key [index_name] [index_type] (index_col_name,……)[index_option]……
- add [constraint] primary key [index_type] (index_col_name,……) [index_option]……
- change [column] old_col_name new_col_name column_definition
- modify [column] col_name column_definition
- dorp column col_name
- dorp primary key
- drop index|key index_name
- dosable|enable keys
- rename [to|as] new_table_name
```mysql
#添加字段
alter table tb1 add soso char(10) not null default 'yes';
#修改字段名称和定义
alter table tb1 change soso justsoso varchar(20) not null;
#添加外键
alter table tb1 add foreign key (col1) references tb2(col1);
#添加unique key
alter table tb1 add unique key (id);
#修改表名
alter table tb1 rename to tb1_new;
- 删除表 ``drop table table_name`` 3. 索引 - 创建索引(使用 help create index; 查看创建索引语法规则) ``create [unique|fulltext|spatial] index index_name on table_name(index_col_name [(length)] [asc|desc],……)``
mysql
create index shadow_index on tb1(shadow);
- 查看索引 ``show indexes from table_name``
mysql> show indexes from tb1;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb1 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
| tb1 | 0 | id | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
| tb1 | 1 | name_index | 1 | name | A | 5 | 5 | NULL | | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
```
- 删除索引(使用 help drop index; 查看删除索引语法规则)
``drop index index_name on table_name``
- 视图:VIEW
- 概念:视图是由一个查询所定义的虚拟表,它与物理表不同的是,视图中的数据没有物理表现形式,除非你为其创建一个索引;视图实际上就是存储下来的select语句。一般情况下,不允许向视图中插入数据。
- 创建视图:CREATE VIEW
create view view_name as ( select * from table_name );
- 删除视图:DROP VIEW
drop view view_name;
- DML语句
- SELECT(分类:简单查询(单表查询)、多表查询、子查询(嵌套查询))
select [distinct] select_list from table_name where qualification
- 单表查询SELECT
- SELECT:要查询的内容
- FROM:要查询的关系,可以是单个表,多个表或者其他select语句
- WHERE:
- 比较操作(=、>、>=、<=、<、<>、!=、<=>、)
select id from tb1 where age>=10;
- 算数操作(+、-、*、/、%),一般不建议使用,因为无法使用索引
select id tb1 where age+1>10;
- 逻辑关系(AND/&&、OR/||、NOT/!)
select id from tb1 where age>10 and sex='M'
- BETWEEN AND
select id from tb1 where age between 10 and 20;
- LIKE '通配符' (%表示任意长度任意字符、_表示任意单个字符)
select id from tb1 where name like '_Y%';
- REGEXP/RLIKE '正则表达式'
select id from tb1 where name rlike '^[xyz].*$';
- IN (list)
select id from tb1 where age in (10,15,20);
- IS NULL、IS NOT NULL
select id from tb1 where name is null;
- 比较操作(=、>、>=、<=、<、<>、!=、<=>、)
- ORDER BY:排序,order by field_name {asc|desc}(代价很大,如果需要经常排序,最好在存储的时候就排好序)
select id from tb1 where age>10 order by id desc;
- AS:别名,可以在select或from中设置别名,别名可以用于比较
select id as student_id from tb1;
select id from tb1 as student_tb1;
- LIMIT:限制,只显示指定数量的结果
select * from tb_name limit [offset,]Count;
select id from tb1 limit 2;
(只显示前两个结果)
select id from tb1 limit 2,3
(跳过前两个结果,只显示三个结果) - 聚合:AVG()、COUNT()、MAX()、MIN()、SUM()
select count(age) from tb1;
- GROUP BY:分组
select * from tb1 group by gender;
- HAVING qualification:用于将GROUP BY的结果再次过滤,having用法跟where一样。
select count(*) as icount,gender from tb1 group by gender having icount>2;
- HAVING qualification:用于将GROUP BY的结果再次过滤,having用法跟where一样。
- 多表查询
- 交叉连接:笛卡尔乘积,显示所有行拼接的结果。
select * from table1,table2;
- 自然连接:将两张表上相同字段的值逐一做连接对比,只保留显示等值关系的。
select * from table1,table2 where table1.classid=table2.classid;
- 外连接:
- 左外连接:
... LEFT JOIN ... ON ...
select tb1.id,tb1.name,tb2.classname from table1 as tb1 left join table2 as tb2 on tb1.classid=tb2.classid;
- 右外连接:
... RIGHT JOIN ... ON ...
select tb1.id,tb1.name,tb2.classname from table1 as tb1 right join table2 as tb2 on tb1.classid=tb2.classid;
- 左外连接:
- 自连接:
select tb1.col1,tb2.col2 from table_name as tb1,table_name as tb2 where tb1.sid=tb2.did;
select tb1.id as admin_id,tb2.classid as guest_id from table1 as tb1,table2 as tb2 where tb1.id=tb2.classid;
- 交叉连接:笛卡尔乘积,显示所有行拼接的结果。
- 子查询:一个查询中嵌套另一个查询
- 在比较运算中使用子查询,子查询返回值必须为单一值
select * from table1 where id > (select avg(id) from table1);
- 在in中使用子查询,子查询结果可以为多个值
select * from table1 where id in (select id from table1 where classid > 2);
- 在from中使用子查询,子查询结果需要定义别名
select name from (select id,name from table1 where classid > 2) as t where t.id > 2;
- 在比较运算中使用子查询,子查询返回值必须为单一值
- 联合查询UNION
(select name,classid from table1) union (select classid,classname from table2);
- INSERT INTO(三种用法)
- 作用是向表中插入指定的数据。在插入数据与已有数据产生冲突的时候,不会进行插入操作。
insert into table_name (col1,col2……) values (va1,val2……),(val3,val4……);
insert into table_name set col1=val1,col2=val2,col3=val3……;
insert into table_name1
(col1,col2,……) (select col1,col2 from table_name2 );``
- 作用是向表中插入指定的数据。在插入数据与已有数据产生冲突的时候,不会进行插入操作。
- REPLACE INTO(三种用法)
- 作用是向表中插入指定的数据,在插入数据与已有数据产生冲突的时候,会插入数据并替换掉原有数据。
replace into table_name (col1,col2……) values (va1,val2……),(val3,val4……);
replace into table_name set col1=val1,col2=val2,col3=val3……;
replace into table_name1
(col1,col2,……) (select col1,col2 from table_name2 );``
- 作用是向表中插入指定的数据,在插入数据与已有数据产生冲突的时候,会插入数据并替换掉原有数据。
- DELETE(where条件与select基本一致)
- 作用是删除指定的记录,但不会清空AUTO_INCREMENT计数器。
delete from table_name where condition
- 作用是删除指定的记录,但不会清空AUTO_INCREMENT计数器。
- UPDATE
- 作用是更新表中指定的数据记录。
update table_name set col1=val1,…… where conditions;
- 作用是更新表中指定的数据记录。
- TRUNCATE
- 作用是清空表并重置AUTO_INCREMENT计数器。
truncate table_name
- 作用是清空表并重置AUTO_INCREMENT计数器。
- SELECT(分类:简单查询(单表查询)、多表查询、子查询(嵌套查询))