MySQL

学习笔记

数据库

复制

net start mysql 
net stop mysql 
mysql -uroot -p123456
show databases;
drop database Test;
use Test;

表

复制

select name, max(age) from user where name is not null group by name having max(age) > 12 order by max(age) limit 0,5

执行顺序：FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVIGN -> SELECT-> DISTINCT -> UNION -> ORDER BY -> LIMIT

复制

插入

INSERT INTO
  table_references
VALUE(S)

复制

查询

SELECT 
  select_expr,...
FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
  [ASC|DESC],...]
[LIMIT {[offset,] row_count}]


完整非select语句
select distinct *
from tablename
where ....
group by ... having ...
order by ...
limit start,count

执行顺序为：
from tablename
where ....
group by ...
select distinct *
having ...
order by ...
limit start,count

复制

修改

UPDATE table_references
  SET col_name1=expr1[, col_name2=expr2...]
[WHERE where_definition]

复制

删除

DELETE FROM tbl_name
[WHERE where_definition]

查看所有表

复制

show tables;

查看表的相关信息

①查看表的结构

复制

desc user;
(show columns from user;)

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(10) | NO   |     | NULL    |                |
| password | varchar(10) | NO   |     | NULL    |                |
| class_id | int(11)     | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

②查看表创建时的信息

复制

show create table user;
(show create table user\G)
(\G按列打印)

*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `password` varchar(10) NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

③查看表本身的相关属性

复制

show index from user;
(show index from user\G)

*************************** 1. row ***************************
        Table: user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 10
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

④查询数据库test中所有表的信息

复制

show table status from test;
(show table status from test\G)

*************************** 1. row ***************************
           Name: class
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 5
    Create_time: 2018-04-13 20:09:19
    Update_time: 2018-04-13 20:17:12
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 10
 Avg_row_length: 1638
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 11
    Create_time: 2018-04-08 14:12:32
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

查询以user开头的表的状态

复制

show table status from test like 'user%'\G

创建表:

创建user表

复制

create table user(
  id int not null auto_increment primary key,
  username varchar(10) not null,
  password varchar(10) not null,
  class_id int not null
);

创建class班级表

复制

create table class(
	id int not null auto_increment primary key,
	name varchar(10) not null,
	master varchar(10) not null
);

判断sql语句检索效率：

复制

desc select * from user where username='user1'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
de         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

修改表名

将user表重命名为student表

复制

rename table user to student;

alter table user1 rename user;

清空表数据(不会清除表结构)

①清空表数据，不会清除自增(继续自增)

复制

delete from user;

②清空表数据，会清除自增(重新开始自增)

复制

truncate table user;

③删除表(删除后表不存在)

复制

drop table user;

增-insert

• 向user表中插入数据

复制

-- value/values

insert into user(username,password,class_id) value('user1','123',1);
insert into user(username,password,class_id) value('user2','123',2);
insert into user(username,password,class_id) value('user3','123',1);
insert into user(username,password,class_id) value('user4','123',3);
insert into user(username,password,class_id) value('user5','123',2);
insert into user(username,password,class_id) values('user6','123',1);
insert into user(username,password,class_id) values('user7','123',2);
insert into user(username,password,class_id) values('user8','123',3);
insert into user(username,password,class_id) values('user9','123',1);
insert into user(username,password,class_id) values('user10','123',2);

insert into user(username,password,class_id) values('user1','123',1),('user2','123',2),('user3','123',1),('user4','123',3),('user5','123',2),('user6','123',1),('user7','123',2),('user8','123',3),('user9','123',1),('user10','123',2);

• 向class表中插入数据

复制

insert into class(name,master) values('Class1','Tom');
insert into class(name,master) values('Class2','Mary');
insert into class(name,master) values('Class3','Linda');
insert into class(name,master) values('Class4','Cindy');

insert into class(name,master) values('Class1','Tom'),('Class2','Mary'),('Class3','Linda'),('Class4','Cindy');

删-delete

复制

delete from user where username='user1';

改-update

复制

update user set password='456' where id=1;
update user set username='user',password='456' where id=1;

查-select

• 1、查询表中所有数据：

复制

select * from user;

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  1 | user1    | 123      |        1 |
|  2 | user2    | 123      |        2 |
|  3 | user3    | 123      |        1 |
|  4 | user4    | 123      |        3 |
|  5 | user5    | 123      |        2 |
|  6 | user6    | 123      |        1 |
|  7 | user7    | 123      |        2 |
|  8 | user8    | 123      |        3 |
|  9 | user9    | 123      |        1 |
| 10 | user10   | 123      |        2 |
+----+----------+----------+----------+

复制

select * from class;

+----+--------+--------+
| id | name   | master |
+----+--------+--------+
|  1 | Class1 | Tom    |
|  2 | Class2 | Mary   |
|  3 | Class3 | Linda  |
|  4 | Class4 | Cindy  |
+----+--------+--------+

• ①查看表中某一列（属性）的值

复制

select username from user;

+----------+
| username |
+----------+
| user1    |
| user2    |
| user3    |
| user4    |
| user5    |
| user6    |
| user7    |
| user8    |
| user9    |
| user10   |
+----------+

• ②去重查询

复制

select distinct password from user;

+----------+
| password |
+----------+
| 123      |
+----------+

• ③模糊查询(查询username中含有user的行)

复制

select * from user where username like '%8';

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  8 | user8    | 123      |        3 |
+----+----------+----------+----------+

• ④起别名

复制

select username 用户名,password 密码 from user;
可用as连接

+-----------+--------+
| 用户名    | 密码   |
+-----------+--------+
| user1     | 123    |
| user2     | 123    |
| user3     | 123    |
| user4     | 123    |
| user5     | 123    |
| user6     | 123    |
| user7     | 123    |
| user8     | 123    |
| user9     | 123    |
| user10    | 123    |
+-----------+--------+

2、查看表中值(不)为null

复制

select * from user where password is (not) null;

3、查看表数据满足条件为…

复制

select * from user where id<=3;

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  1 | user1    | 123      |        1 |
|  2 | user2    | 123      |        2 |
|  3 | user3    | 123      |        1 |
+----+----------+----------+----------+

复制

select * from user where username='user1';

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  1 | user1    | 123      |        1 |
+----+----------+----------+----------+

复制

select * from user where id>=3 and id<=8;
select * from user where id between 3 and 8;(包括3和8)

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  3 | user3    | 123      |        1 |
|  4 | user4    | 123      |        3 |
|  5 | user5    | 123      |        2 |
|  6 | user6    | 123      |        1 |
|  7 | user7    | 123      |        2 |
|  8 | user8    | 123      |        3 |
+----+----------+----------+----------+

复制

select * from user where id=3 or id=8 or id=10;
select * from user where id in(3,8,10);(3或8或10)

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  3 | user3    | 123      |        1 |
|  8 | user8    | 123      |        3 |
| 10 | user10   | 123      |        2 |
+----+----------+----------+----------+

复制

select * from user where username='user1'and password=123;
select * from user where username='user1'or password=123;
select * from user where (username='user1'or password=123) and class_id=1;
…………

4.将表中的值按一定顺序排列。（desc降序，asc升序）

复制

select * from user order by id;
select * from user order by id asc;
select * from user order by id desc;

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
| 10 | user10   | 123      |        2 |
|  9 | user9    | 123      |        1 |
|  8 | user8    | 123      |        3 |
|  7 | user7    | 123      |        2 |
|  6 | user6    | 123      |        1 |
|  5 | user5    | 123      |        2 |
|  4 | user4    | 123      |        3 |
|  3 | user3    | 123      |        1 |
|  2 | user2    | 123      |        2 |
|  1 | user1    | 123      |        1 |
+----+----------+----------+----------+

复制

select * from user order by username desc,password asc;

5、limit限定查询数据条数(分页实现)

①查询前3条数据

复制

select * from user limit 3;

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  1 | user1    | 123      |        1 |
|  2 | user2    | 123      |        2 |
|  3 | user3    | 123      |        1 |
+----+----------+----------+----------+

②从第4条数据开始查询五条数据

复制

select * from user limit 3,5;

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  4 | user4    | 123      |        3 |
|  5 | user5    | 123      |        2 |
|  6 | user6    | 123      |        1 |
|  7 | user7    | 123      |        2 |
|  8 | user8    | 123      |        3 |
+----+----------+----------+----------+

6、concat字符串连接函数

复制

select concat('用户名:',username) name from user;

+------------------+
| name             |
+------------------+
| 用户名:user1     |
| 用户名:user2     |
| 用户名:user3     |
| 用户名:user4     |
| 用户名:user5     |
| 用户名:user6     |
| 用户名:user7     |
| 用户名:user8     |
| 用户名:user9     |
| 用户名:user10    |
+------------------+

复制

select concat('用户名:',username) usn,concat('密码:',password) pwd from user;

+------------------+------------+
| usn              | pwd        |
+------------------+------------+
| 用户名:user1     | 密码:123   |
| 用户名:user2     | 密码:123   |
| 用户名:user3     | 密码:123   |
| 用户名:user4     | 密码:123   |
| 用户名:user5     | 密码:123   |
| 用户名:user6     | 密码:123   |
| 用户名:user7     | 密码:123   |
| 用户名:user8     | 密码:123   |
| 用户名:user9     | 密码:123   |
| 用户名:user10    | 密码:123   |
+------------------+------------+

将id加1然后查询

复制

select (id+1) id from user;

+----+
| id |
+----+
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
+----+

7、rand随机数函数

打印随机数

复制

select rand();

+---------------------+
| rand()              |
+---------------------+
| 0.15174493487155524 |
+---------------------+

按随机数排序并取出第一条数据

复制

select * from user order by rand() limit 1;

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
| 10 | user10   | 123      |        2 |
+----+----------+----------+----------+

8、count统计数量函数

统计一个表的总行数

复制

select count(*) from user;
select count(id) from user;

+----------+
| count(*) |
+----------+
|       10 |
+----------+

统计符合条件(id>=5)的行数

复制

select count(*) from user where id >=5;

+----------+
| count(*) |
+----------+
|        6 |
+----------+

9、sum求和函数

id求和并输出

复制

select sum(id) from user;

+---------+
| sum(id) |
+---------+
|      55 |
+---------+

10、avg求平均值函数

id求平均值并输出

复制

select avg(id) from user;

+---------+
| avg(id) |
+---------+
|  5.5000 |
+---------+

11、max、min求最大、最小值函数

取id最大值并输出

复制

select max(id) from user;

+---------+
| max(id) |
+---------+
|      10 |
+---------+

取id最小值并输出

复制

select min(id) from user;

+---------+
| min(id) |
+---------+
|       1 |
+---------+

12、分组聚合

输出每个班的第一个人

复制

select * from user group by class_id;

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  1 | user1    | 123      |        1 |
|  2 | user2    | 123      |        2 |
|  4 | user4    | 123      |        3 |
+----+----------+----------+----------+

输出每个班的总人数

复制

select concat(class_id,'班') 班级,concat(count(*),'人') 人数 from user group by class_id;

+--------+--------+
| 班级   | 人数   |
+--------+--------+
| 1班    | 4人    |
| 2班    | 4人    |
| 3班    | 2人    |
+--------+--------+

13.普通多表查询

查询每个班里每个人的具体情况

复制

select * from user,class where user.class_id = class.id;

+----+----------+----------+----------+----+--------+--------+
| id | username | password | class_id | id | name   | master |
+----+----------+----------+----------+----+--------+--------+
|  1 | user1    | 123      |        1 |  1 | Class1 | Tom    |
|  2 | user2    | 123      |        2 |  2 | Class2 | Mary   |
|  3 | user3    | 123      |        1 |  1 | Class1 | Tom    |
|  4 | user4    | 123      |        3 |  3 | Class3 | Linda  |
|  5 | user5    | 123      |        2 |  2 | Class2 | Mary   |
|  6 | user6    | 123      |        1 |  1 | Class1 | Tom    |
|  7 | user7    | 123      |        2 |  2 | Class2 | Mary   |
|  8 | user8    | 123      |        3 |  3 | Class3 | Linda  |
|  9 | user9    | 123      |        1 |  1 | Class1 | Tom    |
| 10 | user10   | 123      |        2 |  2 | Class2 | Mary   |
+----+----------+----------+----------+----+--------+--------+

查询1班里每个人的具体情况

复制

select * from user,class where user.class_id = class.id and class.id = 1;

+----+----------+----------+----------+----+--------+--------+
| id | username | password | class_id | id | name   | master |
+----+----------+----------+----------+----+--------+--------+
|  1 | user1    | 123      |        1 |  1 | Class1 | Tom    |
|  3 | user3    | 123      |        1 |  1 | Class1 | Tom    |
|  6 | user6    | 123      |        1 |  1 | Class1 | Tom    |
|  9 | user9    | 123      |        1 |  1 | Class1 | Tom    |
+----+----------+----------+----------+----+--------+--------+

14.嵌套查询或子查询

查询user表里每个班级的相关信息

复制

select * from class where id in (select distinct class_id from user);

(普通多表查询实现相同功能)

复制

select class.* from user,class where user.class_id = class.id group by class.id;

+----+--------+--------+
| id | name   | master |
+----+--------+--------+
|  1 | Class1 | Tom    |
|  2 | Class2 | Mary   |
|  3 | Class3 | Linda  |
+----+--------+--------+

15.连接查询

①左连接

总计每个班的总人数(要求班里没有人的也要显示)

复制

select class.name 班级, count(user.id) 人数 from class left join user on class.id = user.class_id group by class.id;

②右连接

复制

select class.name 班级, count(user.id) 人数 from user right join class on class.id = user.class_id group by class.id;

③内连接

复制

select user.username, class.name from user inner join class on user.class_id = class.id;

(与普通多表查询一样)

复制

select user.username, class.name from user, class where user.class_id = class.id;

复制

+----------+--------+
| username | name   |
+----------+--------+
| user1    | Class1 |
| user2    | Class2 |
| user3    | Class1 |
| user4    | Class3 |
| user5    | Class2 |
| user6    | Class1 |
| user7    | Class2 |
| user8    | Class3 |
| user9    | Class1 |
| user10   | Class2 |
+----------+--------+

属性（字段）

删除某一属性（字段）

复制

alter table user drop username;

为字段添加默认值

复制

alter table user alter age set default 20

删除字段的默认值

复制

alter table user alter age drop default

修改列（属性）名

复制

alter table user change username newusername varchar(30) not null;

将表中某一列某一数据改成另一数据

复制

update user set username='user7' where username='user1';

将表中某一列属性中，属性为某一数据的所在行删除（删除所有username=’Tom’的行）

复制

delete from user where username='Tom';

Mysql数据库优化

Mysql的基础操作

1.Mysql表复制(两步)

①复制表结构

复制

create table student like user;

②复制表内容

复制

insert into student select * from user;

2.Mysql索引

①查看索引

复制

show index from user\G

②普通索引

创建

复制

create index i_age on user(age);
或
alter table user add index i_age(age);

删除

复制

drop index i_age on user;
或
alter table user drop index i_age;

③唯一索引

创建

复制

create unique index u_username on user(username);
或
alter table user add unique(username);

删除

复制

drop index u_username on user;
或
alter table user drop index u_username;

④主键索引

创建

复制

创建表时直接在属性后加primary key
或
创建表时在最后加primary key(属性)
或
alter table user add primary key(id);

删除

复制

alter table user modify id int not null;(先删除自增，若存在)
alter table user drop primary key;(删除主键)

3.Mysql视图(虚拟表，简化多次使用复杂的sql语句。表中数据发生变化时视图里的数据也会随之发生变化)

创建

复制

create view userclass as select user.username,class.name from user,class where user.class_id = class.id;

删除

复制

drop view userclass;

查看

复制

show tables;

查看视图结构

复制

desc userclass;

查看视图数据

复制

select * from userclass;

4.Mysql内置函数

字符串函数：型

①字符串连接concat()

复制

select concat('Hello',' World');

+--------------------------+
| concat('Hello',' World') |
+--------------------------+
| Hello World              |
+--------------------------+

②转换小写lcase()

复制

select lcase('HELLO WORLD');
+----------------------+
| lcase('HELLO WORLD') |
+----------------------+
| hello world          |
+----------------------+

③转换大写ucase()

复制

select ucase('hello world');

+----------------------+
| ucase('hello world') |
+----------------------+
| HELLO WORLD          |
+----------------------+

④长度length()

复制

select length('Hello World');

+-----------------------+
| length('Hello World') |
+-----------------------+
|                    11 |
+-----------------------+

⑤去除左边空格ltrim()

复制

select length(ltrim('   Hello'));

+---------------------------+
| length(ltrim('   Hello')) |
+---------------------------+
|                         5 |
+---------------------------+

⑥去除右边空格rtrim()

复制

select length(rtrim('Hello'   ));

+---------------------------+
| length(rtrim('Hello'   )) |
+---------------------------+
|                         5 |
+---------------------------+

⑦重复repeat()

复制

select repeat('*',5);

+---------------+
| repeat('*',5) |
+---------------+
| *****         |
+---------------+

⑧替换replace()

复制

select replace('Hello World','Hello','Hi');

+-------------------------------------+
| replace('Hello World','Hello','Hi') |
+-------------------------------------+
| Hi World                            |
+-------------------------------------+

⑨截取substring()

复制

select substring('/www/root/php',5,4);

+--------------------------------+
| substring('/www/root/php',5,4) |
+--------------------------------+
| /roo                           |
+--------------------------------+

复制

select substring('/www/root/php',6,4);

+--------------------------------+
| substring('/www/root/php',6,4) |
+--------------------------------+
| root                           |
+--------------------------------+

⑩空格space()

复制

select concat('Hello',space(10),'World') space;

+----------------------+
| space                |
+----------------------+
| Hello          World |
+----------------------+

数学函数：

①转换二进制bin()

复制

select bin(10);
+---------+
| bin(10) |
+---------+
| 1010    |
+---------+

②取大于它的最小整数ceiling()

复制

select ceiling(10.4);

+---------------+
| ceiling(10.4) |
+---------------+
|            11 |
+---------------+

③取小于它的最大整数floor()

复制

select floor(10.4);

+-------------+
| floor(10.4) |
+-------------+
|          10 |
+-------------+

④最大值max()

复制

select max(id) from user;

⑤最小值min()

复制

select min(id) from user;

⑥平方根sqrt()

复制

select sqrt(100);

+-----------+
| sqrt(100) |
+-----------+
|        10 |
+-----------+

⑦随机数rand()

复制

select rand();

+--------------------+
| rand()             |
+--------------------+
| 0.6759574186764261 |
+--------------------+

随机排序

复制

select * from user order by rand();

日期函数：

①当前日期curdate()

复制

select curdate();

+------------+
| curdate()  |
+------------+
| 2018-04-17 |
+------------+

②当前时间curtime()

复制

select curtime();

+-----------+
| curtime() |
+-----------+
| 20:53:19  |
+-----------+

③当前日期时间now()

复制

select now();

+---------------------+
| now()               |
+---------------------+
| 2018-04-17 20:53:09 |
+---------------------+

④当前时间戳unix_timestamp()

复制

select unix_timestamp();

+------------------+
| unix_timestamp() |
+------------------+
|       1523969564 |
+------------------+

⑤返回时间戳对应日期时间from_unixtime()

复制

select from_unixtime(1523969564);

+---------------------------+
| from_unixtime(1523969564) |
+---------------------------+
| 2018-04-17 20:52:44       |
+---------------------------+

⑥返回第几周week()

复制

select week('2018-4-17');

+-------------------+
| week('2018-4-17') |
+-------------------+
|                15 |
+-------------------+

⑦返回年数year()

复制

select year('2018-4-17');

+-------------------+
| year('2018-4-17') |
+-------------------+
|              2018 |
+-------------------+

⑧返回时间差datediff()

复制

select datediff('2018-4-12','2018-4-19');

+-----------------------------------+
| datediff('2018-4-12','2018-4-19') |
+-----------------------------------+
|                                -7 |
+-----------------------------------+

复制

select datediff('2018-4-12 10:15:48','2018-4-19 18:21:32');

+-----------------------------------------------------+
| datediff('2018-4-12 10:15:48','2018-4-19 18:21:32') |
+-----------------------------------------------------+
|                                                  -7 |
+-----------------------------------------------------+

5.重排auto_increment值
①delete

复制

delete from user;
alter user modify auto_increment = 1;

②truncate

复制

truncate table user;

二、常用的sql技巧

1.Mysql中help命令

复制

? create

Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   CREATE FUNCTION UDF
   CREATE INDEX
   CREATE LOGFILE GROUP
   CREATE PROCEDURE
   CREATE SERVER
   CREATE TABLE
   CREATE TABLESPACE
   CREATE TRIGGER
   CREATE USER
   CREATE VIEW
   SHOW
   SHOW CREATE DATABASE
   SHOW CREATE EVENT
   SHOW CREATE FUNCTION
   SHOW CREATE PROCEDURE
   SHOW CREATE TABLE
   SHOW CREATE USER
   SPATIAL

复制

? date%

Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   DATE
   DATE FUNCTION
   DATEDIFF
   DATETIME
   DATE_ADD
   DATE_FORMAT
   DATE_SUB
categories:
   Date and Time Functions

2.随机取出前三行rand()

复制

select * from user order by rand() limit 3;

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  5 | user5    | 123      |        2 |
|  8 | user8    | 123      |        3 |
| 10 | user10   | 123      |        2 |
+----+----------+----------+----------+

3.Mysql正则表达式

复制

以user开头

select * from user where username regexp '^user';
+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  1 | user1    | 123      |        1 |
|  2 | user2    | 123      |        2 |
|  3 | user3    | 123      |        1 |
|  4 | user4    | 123      |        3 |
|  5 | user5    | 123      |        2 |
|  6 | user6    | 123      |        1 |
|  7 | user7    | 123      |        2 |
|  8 | user8    | 123      |        3 |
|  9 | user9    | 123      |        1 |
| 10 | user10   | 123      |        2 |
+----+----------+----------+----------+

复制

以6结尾

select * from user where username regexp '6$';

+----+----------+----------+----------+
| id | username | password | class_id |
+----+----------+----------+----------+
|  6 | user6    | 123      |        1 |
+----+----------+----------+----------+

三、sql语句优化

1.SQL语句优化

①优化SQl语句的一般步骤

1)通过show status命令了解SQL执行的频率

复制

show [session | global] status;
默认session表示当前连接

show status like "Com_%";
show status like "Com_insert%";
show status like "Com_delete%";
show status like "Com_update%";
show status like "Com_select%";

复制

global表示数据库启动当前

show global status like "Com_%";
show global status like "Com_insert%";
show global status like "Com_delete%";
show global status like "Com_update%";
show global status like "Com_select%";

(show status like "InnoDB_rows%";)
(show global status like "InnoDB_rows%";)

2)定位执行效率低下的SQL语句

◇ explain或desc(已知SQL语句)

复制

explain select * from user where username='user1';
desc select * from user where username='user1';

◇ 查看MySQL的慢查询日志(未知SQL语句)

※ 查看慢查询日志是否开启

复制

show variables like "%quer%";

※ 查看慢查询的次数

复制

show status like "%quer%";

※ 修改慢查询时间(my.ini)

复制

long_query_time=5;

重启服务

复制

(测试：快速插入上万条数据,从自身插入数据，以2倍速度插入)
create table t2 like user;
insert into t2 select * from user;
insert into t2(username, password, class_id) select username, password class_id from t2;

InnoDB引擎中表数据和和索引存储在一起(.frm存储表定义和结构，.ibd存储表的数据和索引，ibdata1共享表空间，存储表数据)
MyISAM引擎中表数据和索引是分开储存的(.frm存储表定义和结构，.MYD存储表数据，.MYI存储表索引)

②索引优化

③check与optimize使用方法

④常用SQL的优化