MySQL 数据库常见查询

查询语句

select 【select选项】|*|字段列表|字段名【as】字段别名 from 表名【as】表别名【where子句】【group by子句】【having子句】【order by子句】【limit子句】
说明：
【where子句】【group by子句】【having子句】【order by子句】【limit子句】五子句，可以随意的组合，但无论如何组合，五子句的顺序一定要按语法的顺序书写。
王狗和欧丽

where子句

where后面跟的是条件，在数据源中进行筛选

in | not in

..where 字段 in('北京','上海');

between…and|not between…and

..where 字段 between 25 and 28;

is null | is not null

..where 字段1 is null or 字段2 is null;

模糊查询（like）

..where 字段 like '%永%';
..where 字段 like 'character\_set\_%';

group 分组查询

group by 【分组查询】

..group by 字段;

回溯统计【with rollup】

..group by 字段 with rollup;

having条件

where是对原始数据进行筛选，having是对记录集(结果集)进行筛选
select stusex,count(*) total from stu group by stusex having total>3;

order by排序

asc：升序【默认】
desc：降序
..from 表名 order by 字段 desc;

limit 限制

语法：limit 起始位置，显示长度
起始位置可以省略，默认是从0开始
..from 表名 order by 字段 desc limit 0,3;

查询选项条件

all：显示所有数据
select all 字段 from 表名;
distinct：去除结果集中重复的数据
select distinct 字段 from 表名;

insert…select…(复制表)

创建一个和stu一样结构的表stu1
create table stu1 like stu;
将上海的学生插入stu1表中
insert into stu1 select * from stu where stuaddress='上海';

on duplicate key update

在插入数据的时候，如果插入的数据不满足主键约束或唯一约束则执行更新操作。

– 创建表
create table emp(
id int primary key,
name varchar(20) unique
);
– 插入测试数据
insert into emp values (1,'tom');
- 测试数据
mysql>insert into emp values (1,'berry') on duplicate key update name='berry';
mysql>insert into emp values (2,'berry') on duplicate key update id=2;
mysql>insert into emp values (2,'tom') on duplicate key update id=2,name='tom';

几个关键词

distinct：去除select结果集中重复的数据
engine=innodb或myisam 存储引擎
innodb：5.5版本以上，引擎支持事务安全（提供：commit、rollback功能），且支持外键
在data目录创建一个.frm的表结构文件，数据文件与索引文件，被保存在data目录的ibdata1文件中
myisam引擎中：.frm结构文件 .MYD数据文件 .MYI索引文件
charset :字符集
collate :校对集
with rollup :回溯统计
dual :表（伪表）->from dual

注意：

select 嵌套 select 子句必须使用括号括起来，同时起个别名

union 纵向联合

作用：将多个select语句结果集纵向联合起来
语法：select 语句 union [选项] select 语句 union [选项] select 语句
select stuname from stu union [all | distinct] select stuname from stu1;

小常识大问题，字段相关用反斜线，属性内容相关用引号 ‘’;

主键删除

auto_increment一定是一个int系列的主键或唯一键

修改主键是不需要增加primary key
alter table 表名 modify id int auo_increment;
不是alter table 表名 modify id int auo_increment primary key;

由于unique是一种索引，删除时使用固定语法
alter table 表名 drop index 唯一键名

唯一键名默认是字段名

MySQL约束

MySQL约束存在information_schema 数据库的table_constraints 中，可以通过该表查询约束信息；
约束主要完成对数据的检验，保证数据库数据的完整；如果有相互依赖数据，保证该数据不被删除。

常用五类约束：
not null：非空约束，指定某列不能为空
unique：唯一约束，指定某列和几列组合的数据不能重复
primary key：主键约束，指定某列的数据不能重复唯一
foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据
check：检查，指定一个表达式，用于检验指定数据（MySQL不支持check约束，但可以使用，没有任何效果）

虽然唯一约束不允许出现重复的值，但是可以为多个null，同一个表可以有多个唯一约束，多个列组合的约束

MySQL会给唯一约束的列上默认创建一个唯一索引。
MySQL中 auto_increment 必须是主键，但主键不一定是自动增长的

Mac 使用XAMPP配置SQL-model

松散模式与严格模式

问题：使用枚举类型，可以插入未列举选项为空字符

mysql> create table test(
    -> id int auto_increment primary key,
    -> sex enum('M','W','no')
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test values (1,'A');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from test;
+----+------+
| id | sex  |
+----+------+
|  1 |      |
+----+------+
1 row in set (0.01 sec)

进程解决方案如下(单次解决)：

mysql> show variables like '%sql%%';
+---------------------------+------------------------+
| Variable_name             | Value                  |
+---------------------------+------------------------+
| slave_sql_verify_checksum | ON                     |
| sql_auto_is_null          | OFF                    |
| sql_big_selects           | ON                     |
| sql_buffer_result         | OFF                    |
| sql_log_bin               | ON                     |
| sql_log_off               | OFF                    |
| sql_mode                  | NO_ENGINE_SUBSTITUTION |
| sql_notes                 | ON                     |
| sql_quote_show_create     | ON                     |
| sql_safe_updates          | OFF                    |
| sql_select_limit          | 18446744073709551615   |
| sql_slave_skip_counter    | 0                      |
| sql_warnings              | OFF                    |
+---------------------------+------------------------+
13 rows in set (0.01 sec)

mysql> set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.01 sec)

结果：

mysql> show variables like '%sql%%';
+---------------------------+----------------------------------------------------------------+
| Variable_name             | Value                                                          |
+---------------------------+----------------------------------------------------------------+
| slave_sql_verify_checksum | ON                                                             |
| sql_auto_is_null          | OFF                                                            |
| sql_big_selects           | ON                                                             |
| sql_buffer_result         | OFF                                                            |
| sql_log_bin               | ON                                                             |
| sql_log_off               | OFF                                                            |
| sql_mode                  | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| sql_notes                 | ON                                                             |
| sql_quote_show_create     | ON                                                             |
| sql_safe_updates          | OFF                                                            |
| sql_select_limit          | 18446744073709551615                                           |
| sql_slave_skip_counter    | 0                                                              |
| sql_warnings              | OFF                                                            |
+---------------------------+----------------------------------------------------------------+
13 rows in set (0.00 sec)

mysql> desc test;
+-------+--------------------+------+-----+---------+----------------+
| Field | Type               | Null | Key | Default | Extra          |
+-------+--------------------+------+-----+---------+----------------+
| id    | int(11)            | NO   | PRI | NULL    | auto_increment |
| sex   | enum('M','W','no') | YES  |     | NULL    |                |
+-------+--------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> insert into test value (null,'k');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

补充优化知识：

第一部分

字段创建
索引
引擎-事物回滚-innodb:金融

第二部分

语句优化使用较少的条件过滤结果

第三部分

分区

第四部分

mysql 事物、视图

第五部分

PDO数据层