MySQL基本操作

一、null的注意事项

1. null意味着没有值 或者 未知值
2. 可以测试某个值是否为null is null
3. 不能对null进行算数运算
4. 所有和null进行运算的都为null

---

二、mysql的索引

1. 主键索引 primary key
2. 唯一索引 unique
3. 常规索引 index
4. 全文索引 fulltext

PRI主键约束； UNI唯一约束； MUL可以重复。

(1) 主键索引

主键索引是数据库中最常见的索引类型， 主要确定数据表里数据记录的位置，给字段添加 primary key 来确定索引值

注意：

1. 每个表中 只能有一个主键索引

2. 每个表中最好有一个主键索引 并不是必须的

3. 主键索引可以有很多的候选项 （auto_increment，not null）

4. 当表中的数据 被删除以后 auto_increment 依然记录着下一个数据插入的行号值

   • truncate 表名 清空表 并将自增归位 -----> truncate myindex;
   • alter table 表名 auto_increment=1

实例

mysql> create table myindex(
    -> id int unsigned primary key auto_increment not null
    -> );

(2) 唯一索引

唯一索引和主键索引 都一样，不能插入重复的值；不同的是 主键一个表只能存在一个，唯一索引可以存在多个

实例

mysql> create table myunique(
    -> username varchar(20) unique not null
    -> );

(3) 常规索引

常规索引 只作为提高数据的查询效率来使用的

缺点：

1. 提高了查询效率 但是降低了增删改的效率
2. 索引文件 占用磁盘空间

数据库文件存放的位置

windwos: C:\ProgramData\MySQL\MySQL Server 5.7\Data\performance_schema

ubuntu: /var/lib/mysql

(4) 全文索引

fulltext

alter table 表名 add fulltext 索引名称(索引字段)

创建表并添加索引的完整写法

mysql> create table user(
-> id int unsigned primary key auto_increment not null,
-> username varchar(20) unique,
-> age tinyint,
-> index(age)
-> #unique(username)
-> );

#给索引添加索引名称
mysql> create table user2(
    -> username varchar(20),
    -> age tinyint,
    -> unique myname(username),
    -> index aindex(age)
    -> );

例子的创建表

mysql> create table test(
    -> id int unsigned primary key auto_increment not null,
    -> username varchar(50) not null,
    -> userpass varchar(50) not null,
    -> telno varchar(20) not null,
    -> sex enum('w','m') not null default 'm',
    -> birthday date not null default '0000-00-00',
    -> index(username),
    -> index(userpass),
    -> unique(telno)
    -> );

---

三、表的存储类型

MyISAM 和 InnoDB

修改类型

alter table 表名 engine=表存储类型;

MyISAM和InnoDB的区别

1. MyISAM表的存储文件为3个，InnoDB为2个
2. MyISAM不支持事物 innodb支持
3. MyISAM不支持外键 innodb支持
4. MyISAM表的查询效率高于innodb，但是innodb的安全性高于MyISAM

(1) MyISAM的文件说明

1. frm文件 存储当前表结构的文件

   在innodb和MyISAM中都存在

2. MYD：即MY DATA 存储表数据的文件

3. MYI：即 MY INDEX 存储表索引的文件

(2) InnoDB的文件说明

1. frm文件 存储当前表结构的文件

   在innodb和MyISAM中都存在

2. ibd 存储表数据和索引

(3) innodb事物处理操作

1. 将当前表文件存储类型改为 innodb

   alter table 表名 engine=innodb;

2. 查看当前表的提交类型

   select @@autocommit

   如果值为1 则为自动提交

3. 改为手动提交（开启事物）

   set autocommit=0

4. 事物开始

   begin;

5. 执行各种SQL语句

6. 提交或者回滚

   1. commit work;
   2. rollback work;

注意：

事物是针对库中表的数据来操作的 并不是针对你当前的库 如果库被干掉了，那就一切都不存在。

mysql> show tables;
+--------------------+
| Tables_in_practice |
+--------------------+
| user               |
| user2              |
+--------------------+
2 rows in set (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
Empty set (0.00 sec)

mysql> desc user;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)      | YES  | UNI | NULL    |                |
| age      | tinyint(4)       | YES  | MUL | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into user values(null, '张三', 20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | 张三     |   20 |
+----+----------+------+
1 row in set (0.00 sec)

mysql> commit work;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(null, '李四', 21);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | 张三     |   20 |
|  2 | 李四     |   21 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> rollback work;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | 张三     |   20 |
+----+----------+------+
1 row in set (0.00 sec)

mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

这里的auto-increment变成3了！说明有数据来过，但是又没有保留下来。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(null, '王武', 22);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | 张三     |   20 |
|  3 | 王武     |   22 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> \q   # 退出
Bye

C:\Users\zyp>mysql -uroot -p  # 重启mysql
Enter password: ****
Welcome to the MySQL monitor.

mysql> use practice;
Database changed
mysql> select * from user;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | 张三     |   20 |
+----+----------+------+
1 row in set (0.01 sec)

mysql> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

---

四、更改表结构

mysql> use practice;
Database changed
mysql> select * from user;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | 张三     |   20 |
|  4 | 赵柳     |   23 |
+----+----------+------+
2 rows in set (0.00 sec)

1. 给当前的表添加一个新的字段 add

alter table user add sex enum('w','m') default 'w';

mysql> alter table user add sex enum('w','m') default 'w';
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+----------+------+------+
| id | username | age  | sex  |
+----+----------+------+------+
|  1 | 张三     |   20 | w    |
|  4 | 赵柳     |   23 | w    |
+----+----------+------+------+
2 rows in set (0.00 sec)

2. 删除一个字段drop

alter table user drop sex/id /info/age/money....

mysql> alter table user drop sex;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | 张三     |   20 |
|  4 | 赵柳     |   23 |
+----+----------+------+
2 rows in set (0.00 sec)

3. 添加新字段在第一位 first

alter table user add info varchar(100) default '个人说明' first;

mysql> alter table user add info varchar(100) default '个人说明' first;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----------+----+----------+------+
| info     | id | username | age  |
+----------+----+----------+------+
| 个人说明 |  1 | 张三     |   20 |
| 个人说明 |  4 | 赵柳     |   23 |
+----------+----+----------+------+
2 rows in set (0.00 sec)

1. 添加新字段在某个字段的后面 after

alter table user add backinfo varchar(100) default '备注' after age;

mysql> alter table user add backinfo varchar(100) default '备注' after age;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----------+----+----------+------+----------+
| info     | id | username | age  | backinfo |
+----------+----+----------+------+----------+
| 个人说明 |  1 | 张三     |   20 | 备注     |
| 个人说明 |  4 | 赵柳     |   23 | 备注     |
+----------+----+----------+------+----------+
2 rows in set (0.00 sec)

4. 更改字段名称并更改字段顺序 change

mysql> alter table user change age money decimal(6,2);
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----------+----+----------+-------+----------+
| info     | id | username | money | backinfo |
+----------+----+----------+-------+----------+
| 个人说明 |  1 | 张三     | 20.00 | 备注     |
| 个人说明 |  4 | 赵柳     | 23.00 | 备注     |
+----------+----+----------+-------+----------+
2 rows in set (0.01 sec)

mysql> alter table user change money age tinyint;
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----------+----+----------+------+----------+
| info     | id | username | age  | backinfo |
+----------+----+----------+------+----------+
| 个人说明 |  1 | 张三     |   20 | 备注     |
| 个人说明 |  4 | 赵柳     |   23 | 备注     |
+----------+----+----------+------+----------+
2 rows in set (0.01 sec)

mysql> alter table user change age money decimal(6,2) after backinfo;
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----------+----+----------+----------+-------+
| info     | id | username | backinfo | money |
+----------+----+----------+----------+-------+
| 个人说明 |  1 | 张三     | 备注     | 20.00 |
| 个人说明 |  4 | 赵柳     | 备注     | 23.00 |
+----------+----+----------+----------+-------+
2 rows in set (0.01 sec)

5. 更改字段类型 modify

mysql> alter table user modify username char(11);
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+------------------+------+-----+----------+----------------+
| Field    | Type             | Null | Key | Default  | Extra          |
+----------+------------------+------+-----+----------+----------------+
| info     | varchar(100)     | YES  |     | 个人说明 |                |
| id       | int(10) unsigned | NO   | PRI | NULL     | auto_increment |
| username | char(11)         | YES  | UNI | NULL     |                |
| backinfo | varchar(100)     | YES  |     | 备注     |                |
| money    | decimal(6,2)     | YES  | MUL | NULL     |                |
+----------+------------------+------+-----+----------+----------------+
5 rows in set (0.01 sec)

6. 添加字段索引

alter table user add index(username);  # 添加常规索引

alter table user add unique(username);  # 添加唯一索引

alter table user add index infoindex(info);  # 添加索引并添加索引名

7. 删除索引

alter table user drop key username;

8. 创建一个表b和表a一样

create table copy_user like user;

9. 查看所有的索引

show index from 表名;

mysql> show index from user;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          0 | username_3 |            1 | username    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | age        |            1 | money       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | username_2 |            1 | username    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | infoindex  |            1 | info        | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

10. 更改字段的字符集

alter database  xxx_ku  character set utf8;

alter table  xxx(user)  character set utf8;

alter table  xxx(user)  modify username varchar(20) character set utf8;

---

五、INSERT 数据的添加

mysql> desc user;
+----------+------------------+------+-----+----------+----------------+
| Field    | Type             | Null | Key | Default  | Extra          |
+----------+------------------+------+-----+----------+----------------+
| info     | varchar(100)     | YES  | MUL | 个人说明 |                |
| id       | int(10) unsigned | NO   | PRI | NULL     | auto_increment |
| username | char(11)         | YES  | UNI | NULL     |                |
| backinfo | varchar(100)     | YES  |     | 备注     |                |
| money    | decimal(6,2)     | YES  | MUL | NULL     |                |
+----------+------------------+------+-----+----------+----------------+
5 rows in set (0.00 sec)

1. 指定字段名称添加数据

insert into user(username, money) values('王五', 24);

mysql> insert into user(username, money) values('王五', 24);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----------+----+----------+----------+-------+
| info     | id | username | backinfo | money |
+----------+----+----------+----------+-------+
| 个人说明 |  1 | 张三     | 备注     | 20.00 |
| 个人说明 |  4 | 赵柳     | 备注     | 23.00 |
| 个人说明 |  6 | 李斯     | 备注     | 21.10 |
| 个人说明 |  7 | 王五     | 备注     | 24.00 |
+----------+----+----------+----------+-------+
4 rows in set (0.00 sec)

需要注意：如果有字段不为空且没有默认值 那么必须插入值

2. 不指定字段添加值（需要将所有字段都插入值）

insert into user values('个人说明', null, '钱强','备注', 25);

mysql> insert into user values('个人说明', null, '钱强','备注', 25);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----------+----+----------+----------+-------+
| info     | id | username | backinfo | money |
+----------+----+----------+----------+-------+
| 个人说明 |  1 | 张三     | 备注     | 20.00 |
| 个人说明 |  4 | 赵柳     | 备注     | 23.00 |
| 个人说明 |  6 | 李斯     | 备注     | 21.10 |
| 个人说明 |  7 | 王五     | 备注     | 24.00 |
| 个人说明 |  8 | 钱强     | 备注     | 25.00 |
+----------+----+----------+----------+-------+
5 rows in set (0.00 sec

3. 指定字段添加多个值

insert into user(username, money) values('诸葛',26),('孔明',27);

mysql> insert into user(username, money) values('诸葛',26),('孔明',27);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----------+----+----------+----------+-------+
| info     | id | username | backinfo | money |
+----------+----+----------+----------+-------+
| 个人说明 |  1 | 张三     | 备注     | 20.00 |
| 个人说明 |  4 | 赵柳     | 备注     | 23.00 |
| 个人说明 |  6 | 李斯     | 备注     | 21.10 |
| 个人说明 |  7 | 王五     | 备注     | 24.00 |
| 个人说明 |  8 | 钱强     | 备注     | 25.00 |
| 个人说明 |  9 | 诸葛     | 备注     | 26.00 |
| 个人说明 | 10 | 孔明     | 备注     | 27.00 |
+----------+----+----------+----------+-------+
7 rows in set (0.00 sec)

4. 不指定字段添加多个值

insert into user values('个人说明',null,'欧阳','备注',28),('个人说明',null,'夏丹','备注',29);

mysql> insert into user values('个人说明',null,'欧阳','备注',28),('个人说明',null,'夏丹','备注',29);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----------+----+----------+----------+-------+
| info     | id | username | backinfo | money |
+----------+----+----------+----------+-------+
| 个人说明 |  1 | 张三     | 备注     | 20.00 |
| 个人说明 |  4 | 赵柳     | 备注     | 23.00 |
| 个人说明 |  6 | 李斯     | 备注     | 21.10 |
| 个人说明 |  7 | 王五     | 备注     | 24.00 |
| 个人说明 |  8 | 钱强     | 备注     | 25.00 |
| 个人说明 |  9 | 诸葛     | 备注     | 26.00 |
| 个人说明 | 10 | 孔明     | 备注     | 27.00 |
| 个人说明 | 11 | 欧阳     | 备注     | 28.00 |
| 个人说明 | 12 | 夏丹     | 备注     | 29.00 |
+----------+----+----------+----------+-------+
9 rows in set (0.00 sec)

让字段可以重复，去掉key

alter table user drop key username;

5. 改字段中的某个信息：update

update user set username='王二' where id=7;

mysql> update user set username='王二' where id=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----------+----+----------+----------+-------+
| info     | id | username | backinfo | money |
+----------+----+----------+----------+-------+
| 个人说明 |  1 | 张三     | 备注     | 20.00 |
| 个人说明 |  4 | 赵柳     | 备注     | 23.00 |
| 个人说明 |  6 | 李斯     | 备注     | 21.10 |
| 个人说明 |  7 | 王二     | 备注     | 24.00 |
| 个人说明 |  8 | 钱强     | 备注     | 25.00 |
| 个人说明 |  9 | 诸葛     | 备注     | 26.00 |
| 个人说明 | 10 | 孔明     | 备注     | 27.00 |
| 个人说明 | 11 | 欧阳     | 备注     | 28.00 |
| 个人说明 | 12 | 夏丹     | 备注     | 29.00 |
+----------+----+----------+----------+-------+
9 rows in set (0.00 sec)

---

六、SELECT 数据的查询

1. 不指定字段查询（查询所有字段）

select * from 表名;

mysql> select * from user;
+----------+----+----------+----------+-------+
| info     | id | username | backinfo | money |
+----------+----+----------+----------+-------+
| 个人说明 |  1 | 张三     | 备注     | 20.00 |
| 个人说明 |  4 | 赵柳     | 备注     | 23.00 |
| 个人说明 |  6 | 李斯     | 备注     | 21.10 |
| 个人说明 |  7 | 王二     | 备注     | 24.00 |
| 个人说明 |  8 | 钱强     | 备注     | 25.00 |
| 个人说明 |  9 | 诸葛     | 备注     | 26.00 |
| 个人说明 | 10 | 孔明     | 备注     | 27.00 |
| 个人说明 | 11 | 欧阳     | 备注     | 28.00 |
| 个人说明 | 12 | 夏丹     | 备注     | 29.00 |
+----------+----+----------+----------+-------+
9 rows in set (0.00 sec)

2. 指定字段查询

select 字段名1,字段名2... from 表名;

mysql> select username from user;
+----------+
| username |
+----------+
| 夏丹     |
| 孔明     |
| 张三     |
| 李斯     |
| 欧阳     |
| 王二     |
| 诸葛     |
| 赵柳     |
| 钱强     |
+----------+
9 rows in set (0.00 sec)

3. 查询字段并运算

select id+money from user;

mysql> select id + money from user;
+------------+
| id + money |
+------------+
|      21.00 |
|      27.10 |
|      27.00 |
|      31.00 |
|      33.00 |
|      35.00 |
|      37.00 |
|      39.00 |
|      41.00 |
+------------+
9 rows in set (0.00 sec)

4. 起别名

select username as name from 表名;

select username name from 表名;

---

七、where 条件

(1) 比较运算符

1. >
2. <
3. >=
4. <=
5. !=/<>
6. =

(2) 逻辑运算符

1. 逻辑与 and

   select * from user where username='苍老师' and age<=30;

   两侧为真才为真

2. 逻辑或 or

   select * from user where username='苍老师' or age<=30;

   只要满足一个就为真

3. and 和 or 一起使用

   select * from user where id=1 or (age=18 and sex='w');

4. 在…内 in

   select * from user where id in(1,3,5,6,7);

   select * from user where id=1 or id=2 or id=6;

5. 不在…内 not in

   select * from user where id not in(1,3,5,6,7);

   select * from user where id!=1 and id!=2 and id!=6;

6. 在…范围内 between … and …

   select * from user where age between 20 and 33; `

   select * from user where age>=20 and age<=33;

7. 不在…范围内 not between … and …

   select * from user where age not between 20 and 33;

(3) order by 排序

1. 默认升序 asc

   select * from user order by age;

   select * from user order by age asc;

2. 降序 desc

   select * from user order by age desc;

(4) is is not

1. is not

   select * from user where username is not null;

2. is

   select * from user where username is null;

(5) limit

limit num 直接取出num条数据

select * from user order by age desc limit 1; # 取出年龄最大的一条数据

limit x,num 从 x 的位置开始，取出 num 条数据

mysql> select * from user;
+----------+----+----------+----------+-------+
| info     | id | username | backinfo | money |
+----------+----+----------+----------+-------+
| 个人说明 |  1 | 张三     | 备注     | 20.00 |
| 个人说明 |  4 | 赵柳     | 备注     | 23.00 |
| 个人说明 |  6 | 李斯     | 备注     | 21.10 |
| 个人说明 |  7 | 王二     | 备注     | 24.00 |
| 个人说明 |  8 | 钱强     | 备注     | 25.00 |
| 个人说明 |  9 | 诸葛     | 备注     | 26.00 |
| 个人说明 | 10 | 孔明     | 备注     | 27.00 |
| 个人说明 | 11 | 欧阳     | 备注     | 28.00 |
| 个人说明 | 12 | 夏丹     | 备注     | 29.00 |
+----------+----+----------+----------+-------+
9 rows in set (0.00 sec)

mysql> select * from user order by money desc limit 3;  # 取出money最多的3个
+----------+----+----------+----------+-------+
| info     | id | username | backinfo | money |
+----------+----+----------+----------+-------+
| 个人说明 | 12 | 夏丹     | 备注     | 29.00 |
| 个人说明 | 11 | 欧阳     | 备注     | 28.00 |
| 个人说明 | 10 | 孔明     | 备注     | 27.00 |
+----------+----+----------+----------+-------+
3 rows in set (0.00 sec)

mysql> select * from user order by money desc limit 3, 2;  # 从3个位置开始，取出2个钱多的
+----------+----+----------+----------+-------+
| info     | id | username | backinfo | money |
+----------+----+----------+----------+-------+
| 个人说明 |  9 | 诸葛     | 备注     | 26.00 |
| 个人说明 |  8 | 钱强     | 备注     | 25.00 |
+----------+----+----------+----------+-------+
2 rows in set (0.00 sec)

注意：

如果是从头取出num条数据 limit num == limit 0,num

分页计算：

求分页取值的偏移量：（nowPage-1）* everyPage

(6) 模糊查询 like

1. 包含查询

   like ‘%字符%’

2. 以某个字符开头的查询

   like ‘字符%’

3. 以某个字符结尾的查询

   like ‘%字符’

---

八、聚合函数

1. 最大值

max()

select max(age) from user;

mysql> select max(money) from user;
+------------+
| max(money) |
+------------+
|      29.00 |
+------------+
1 row in set (0.00 sec)

2. 最小值

min()

select min(age) from user;

mysql> select min(id) from user;
+---------+
| min(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

3. 统计

count()

select count(*) from user

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

4. 平均值

avg()

select avg(age) from user;

mysql> select avg(money) from user;
+------------+
| avg(money) |
+------------+
|  24.788889 |
+------------+
1 row in set (0.00 sec)

5. 求和

sum()

select sum(age) from user;

mysql> select sum(money) from user;
+------------+
| sum(money) |
+------------+
|     223.10 |
+------------+
1 row in set (0.00 sec)

---

九、group by 分组

查询男女分别多少人

select sex,count(*) from user group by sex;

统计每个年龄段分别多少人

select age,count(*) from user group by age;

统计每个年龄段的男女分别有多少人

select sex,age,count(*) from user group by sex,age;

groub by having 其中的having相当于where

查询每个年龄段人数大于1人的数据

select age,count(*) as count from user group by age having count>1;

查询每个年龄段人数大于1人的数据 并且年龄小于40

select age,count(*) as count from user group by age having count>1 and age<40;

查询每个年龄段人数大于1人的数据 并且年龄为10,20,30

select age,count(*) as count from user group by age having age in(18,20,30);

---

十、delete 删除

delete from 表名 [where…]

注意： where 应该加 如果没有where条件删除所有数据

实例: delete from user where username='xxx';

---

十一、update 修改

update 表名 set 字段名=字段值,[字段名=字段值…[where…]]

注意： 不要忘记where条件 否则修改全部

实例： update user set sex='m' where age in(18,20);

---

十二、多表联查

关联条件

外键

(1) 隐式内连接

select * from user,goods where user.id=goods.uid
select user.username,user.age,goods.goodsname from user,goods where user.id=goods.uid

起别名

select u.username,g.goodsname from user u,goods g where u.id=g.uid

(2) 显示内连接 inner join on

select * from user INNER JOIN goods ON user.id=goods.uid and goods.uid=1

select u.username,g.goodsname from user u INNER JOIN goods g ON u.id=g.uid and g.uid=1

注意： 隐式内连接和显示内连接其实是同一个查询 会将关联的数据全部查询出来

(3) 左链接 left join on

select u.username,g.goodsname from user u LEFT JOIN goods g ON u.id=g.uid

注意： 左链接会将左表作为主表 右表为辅表 会将主表所有数据查询出来 辅表没有关联的数据使用null来占