数据库创建命令

create database if not exists localhost_mayi default character set utf8mb4 collate utf8mb4_unicode_ci;

列出所有的数据库

SHOW DATABASES;

使用某个数据库

use test;

其中,test为数据库名

列出数据库中的所有表

show tables;

创建语句的时候忘记加主键，和主键自增

create table students 
(
        stud_id bigint(20) not null,
        stud_name varchar(255),
        stud_class varchar(255)
);

要验证你的表是否按照预期创建，可以使用desc

desc students;

增加主键和自动属性

alter table add students change stud_id stud_id bigint(20) not null auto_increment primary key;
如果已经增加了主键，但未设置自动
alter table add students change stud_id stud_id bigint(20) not null auto_increment;

增加表的索引，唯一联合索引

create index  class_name on `students` (`stud_class`);
create UNIQUE index stud_class_uniq_idx on  `student_class_relate` (`stud_id`, `class_id`);

修改字段名称

alter table 表名 change 旧名称 新名称 类型及相关属性;
alter table `student_class_relate` change `valid_status` `is_valid` int;  

查询当前日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2019-06-26 |
+------------+
mysql> select month(curdate());
+------------------+
| month(curdate()) |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)
mysql> select month(date_add(curdate(), interval 1 month));
+----------------------------------------------+
| month(date_add(curdate(), interval 1 month)) |
+----------------------------------------------+
|                                            7 |
+----------------------------------------------+
1 row in set (0.01 sec)
mysql> select mod(month(date_add(curdate(), interval 1 month)), 12);
+-------------------------------------------------------+
| mod(month(date_add(curdate(), interval 1 month)), 12) |
+-------------------------------------------------------+
|                                                     7 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

NULL空值

不能逻辑判断符判断NULL值

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+
1 row in set (0.01 sec)

在Group by中两个NUll值被认为是一样的。当做Order by时，如果使用asc排序，NULL值会首先显示，如果用desc排序，NULL值最后展示。

要查找包含五个字符的名称,使用五个_字符

mysql> SELECT * FROM pet WHERE name LIKE '_____';
或者
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
或者
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

RegExp使用正则匹配查询

SELECT * FROM pet WHERE name REGEXP BINARY '^b';

删除索引

drop index stud_class_uniq_idx on `student_class_relate`;

根据一个表的数据更新另一张表的数据

update `docktor` AS t1, `partion` AS t2 set t1.`cur_price` = t2.`cur_price` where t1.`stock_code` = t2.`stock_code` and t1.`time_stamp` = t2.`time_stamp`;

时间戳字段转字符串

select from_unixtime(1451997924,'%Y-%m-%d %H:%I:%s');

查找时间区间内数据

select * from `error_log` where hour(from_unixtime(`enter_time`,'%Y-%m-%d %H:%I:%s'))<9 OR hour(from_unixtime(`enter_time`,'%Y-%m-%d %H:%I:%s'))>16;

查询出生日期到现在过了多少年

SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet;

group by having 组合条件查询

查询宠物店，每个主人拥有多少宠物

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

查找班级人数大于20的所有班级，及其人数
select room_id, count(1) from classroom where is_hide=0 group by room_id having count(1) > 20;

offset 偏移查询

select * from push_rec where `status` = 1  limit 10 offset 10;

ONLY_FULL_GROUP_BY设置后， 如果分组查询时，没有提供group by子句就会报错

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column

用户定义变量

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

EXISTS 和 NOT EXISTS的子查询

如果子查询返回记录，exists子查询就是True, Not exists子查询就是false.通常，一个exists子查询使用start *，但也可以是任何select 5、select column1等任何表达式，mysql忽略这样子查询中的select list，所以这样没什么区别。

select * from content_messages where exists (select * from content_sources where content_messages.source_id=content_sources.id);

sql倒序删除数据

delete from area where id>48 order by id desc

导出数据库

直接使用命令：

mysqldump -u root -p abc >abc.sql

然后回车输入密码就可以了；

mysqldump -u 数据库链接用户名 -p 目标数据库 > 存储的文件名

文件会导出到当前目录下

导入数据库（sql文件）
mysql -u 用户名 -p 数据库名 < 数据库名.sql
mysql -u abc -p abc < abc.sql

注意sql文件必须在当前目录下，如果不在当前目录下需要在< 之后加上具体sql文件路径