Day03.Mysql字段值操作

mysql> show engines;
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.01 sec)
可以在创建表的时候制定引擎
mysql> create table intab (id int(4)) engine=myisam;
Query OK, 0 rows affected (0.07 sec)

mysql> show create table intab;
+——-+——————————————————————————————+
| Table | Create Table |
+——-+——————————————————————————————+
| intab | CREATE TABLE `intab` (
`id` int(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+——-+——————————————————————————————+
1 row in set (0.00 sec)

查看当前锁的状态
mysql> show status like ‘table_lock%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| Table_locks_immediate | 100 |
| Table_locks_waited | 0 |
+———————–+——-+
2 rows in set (0.00 sec)

查看mysql的默认使用目录
mysql> show variables like ‘secure_file_priv’;
+——————+———————–+
| Variable_name | Value |
+——————+———————–+
| secure_file_priv | /var/lib/mysql-files/ |
+——————+———————–+
1 row in set (0.00 sec)
自行到目录去查看一下
[root@50 ~]# ls -ld /var/lib/mysql-files/
drwxr-x—. 2 mysql mysql 6 11月 29 2016 /var/lib/mysql-files/

修改默认目录，并查看
[root@50 ~]# mkdir /myload
[root@50 ~]# chown mysql /myload/
[root@50 ~]# grep -v “^$” /etc/my.cnf | grep -v “^#”
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
secure_file_priv=”/myload”
validate_password_policy=0
validate_password_length=6

[root@50 ~]# systemctl restart mysqld

mysql> show variables like “secure_file_priv”;
+——————+———-+
| Variable_name | Value |
+——————+———-+
| secure_file_priv | /myload/ |
+——————+———-+
1 row in set (0.01 sec)

从文件导入数据
mysql> create table user (username char(20) not null,pass char(5),uid int(5),gid int(5),comment varchar(100),homedir varchar(200),shell varchar(50));
Query OK, 0 rows affected (0.25 sec)

mysql> desc user;
+———-+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+————–+——+—–+———+——-+
| username | char(20) | NO | | NULL | |
| pass | char(5) | YES | | NULL | |
| uid | int(5) | YES | | NULL | |
| gid | int(5) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
| homedir | varchar(200) | YES | | NULL | |
| shell | varchar(50) | YES | | NULL | |
+———-+————–+——+—–+———+——-+
7 rows in set (0.00 sec)

mysql> load data infile “/myload/passwd” into table user fields terminated by “:” lines terminated by “\n”;
Query OK, 41 rows affected (0.08 sec)
Records: 41 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from user;
+———————+——+——-+——-+—————————————————————–+—————————+—————-+
| username | pass | uid | gid | comment | homedir | shell |
+———————+——+——-+——-+—————————————————————–+—————————+—————-+
| root | x | 0 | 0 | root | /root | /bin/bash |
| bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| sync | x | 5 | 0 | sync | /sbin | /bin/sync |
| shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
| halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
| mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
| operator | x | 11 | 0 | operator | /root | /sbin/nologin |
| games | x | 12 | 100 | games | /usr/games | /sbin/nologin |
| ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin |
| nobody | x | 99 | 99 | Nobody | / | /sbin/nologin |
| systemd-network | x | 192 | 192 | systemd Network Management | / | /sbin/nologin |
| dbus | x | 81 | 81 | System message bus | / | /sbin/nologin |
| polkitd | x | 999 | 998 | User for polkitd | / | /sbin/nologin |
| libstoragemgmt | x | 998 | 996 | daemon account for libstoragemgmt | /var/run/lsm | /sbin/nologin |
| rpc | x | 32 | 32 | Rpcbind Daemon | /var/lib/rpcbind | /sbin/nologin |
| colord | x | 997 | 995 | User for colord | /var/lib/colord | /sbin/nologin |
| saslauth | x | 996 | 76 | Saslauthd user | /run/saslauthd | /sbin/nologin |
| abrt | x | 173 | 173 | | /etc/abrt | /sbin/nologin |
| rtkit | x | 172 | 172 | RealtimeKit | /proc | /sbin/nologin |
| radvd | x | 75 | 75 | radvd user | / | /sbin/nologin |
| chrony | x | 995 | 993 | | /var/lib/chrony | /sbin/nologin |
| tss | x | 59 | 59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null | /sbin/nologin |
| usbmuxd | x | 113 | 113 | usbmuxd user | / | /sbin/nologin |
| geoclue | x | 994 | 991 | User for geoclue | /var/lib/geoclue | /sbin/nologin |
| qemu | x | 107 | 107 | qemu user | / | /sbin/nologin |
| rpcuser | x | 29 | 29 | RPC Service User | /var/lib/nfs | /sbin/nologin |
| nfsnobody | x | 65534 | 65534 | Anonymous NFS User | /var/lib/nfs | /sbin/nologin |
| setroubleshoot | x | 993 | 990 | | /var/lib/setroubleshoot | /sbin/nologin |
| pulse | x | 171 | 171 | PulseAudio System Daemon | /var/run/pulse | /sbin/nologin |
| gdm | x | 42 | 42 | | /var/lib/gdm | /sbin/nologin |
| gnome-initial-setup | x | 992 | 987 | | /run/gnome-initial-setup/ | /sbin/nologin |
| sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
| avahi | x | 70 | 70 | Avahi mDNS/DNS-SD Stack | /var/run/avahi-daemon | /sbin/nologin |
| postfix | x | 89 | 89 | | /var/spool/postfix | /sbin/nologin |
| ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin |
| tcpdump | x | 72 | 72 | | / | /sbin/nologin |
| lisi | x | 1000 | 1000 | lisi | /home/lisi | /bin/bash |
| mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false |
+———————+——+——-+——-+—————————————————————–+—————————+—————-+
41 rows in set (0.00 sec)

给上面这个表添加一个id字段，并设为主键
步骤1：添加id字段并设置为not null
mysql> alter table user add id int(5) not null first;
Query OK, 0 rows affected (0.63 sec)
Records: 0 Duplicates: 0 Warnings: 0
步骤2：设置为索引
mysql> create index id on user(id);
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
步骤3：设置为自增
mysql> alter table user modify id int(5) auto_increment not null first;
Query OK, 41 rows affected (0.79 sec)
Records: 41 Duplicates: 0 Warnings: 0
步骤4：设置为主键
mysql> alter table user add primary key(id);
Query OK, 0 rows affected (0.67 sec)
Records: 0 Duplicates: 0 Warnings: 0

步骤5：查看并确认
mysql> desc user;
+———-+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————–+——+—–+———+—————-+
| id | int(5) | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | NULL | |
| pass | char(5) | YES | | NULL | |
| uid | int(5) | YES | | NULL | |
| gid | int(5) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
| homedir | varchar(200) | YES | | NULL | |
| shell | varchar(50) | YES | | NULL | |
+———-+————–+——+—–+———+—————-+
8 rows in set (0.00 sec)

mysql> show create table user;
+——-+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| Table | Create Table |
+——-+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| user | CREATE TABLE `user` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`username` char(20) NOT NULL,
`pass` char(5) DEFAULT NULL,
`uid` int(5) DEFAULT NULL,
`gid` int(5) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL,
`homedir` varchar(200) DEFAULT NULL,
`shell` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1 |
+——-+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)

mysql> select * from user;
+—-+———————+——+——-+——-+—————————————————————–+—————————+—————-+
| id | username | pass | uid | gid | comment | homedir | shell |
+—-+———————+——+——-+——-+—————————————————————–+—————————+—————-+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| 5 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| 6 | sync | x | 5 | 0 | sync | /sbin | /bin/sync |
| 7 | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
| 8 | halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
| 9 | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
| 10 | operator | x | 11 | 0 | operator | /root | /sbin/nologin |
| 11 | games | x | 12 | 100 | games | /usr/games | /sbin/nologin |
| 12 | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin |
| 13 | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin |
| 14 | systemd-network | x | 192 | 192 | systemd Network Management | / | /sbin/nologin |
| 15 | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin |
| 16 | polkitd | x | 999 | 998 | User for polkitd | / | /sbin/nologin |
| 17 | libstoragemgmt | x | 998 | 996 | daemon account for libstoragemgmt | /var/run/lsm | /sbin/nologin |
| 18 | rpc | x | 32 | 32 | Rpcbind Daemon | /var/lib/rpcbind | /sbin/nologin |
| 19 | colord | x | 997 | 995 | User for colord | /var/lib/colord | /sbin/nologin |
| 20 | saslauth | x | 996 | 76 | Saslauthd user | /run/saslauthd | /sbin/nologin |
| 21 | abrt | x | 173 | 173 | | /etc/abrt | /sbin/nologin |
| 22 | rtkit | x | 172 | 172 | RealtimeKit | /proc | /sbin/nologin |
| 23 | radvd | x | 75 | 75 | radvd user | / | /sbin/nologin |
| 24 | chrony | x | 995 | 993 | | /var/lib/chrony | /sbin/nologin |
| 25 | tss | x | 59 | 59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null | /sbin/nologin |
| 26 | usbmuxd | x | 113 | 113 | usbmuxd user | / | /sbin/nologin |
| 27 | geoclue | x | 994 | 991 | User for geoclue | /var/lib/geoclue | /sbin/nologin |
| 28 | qemu | x | 107 | 107 | qemu user | / | /sbin/nologin |
| 29 | rpcuser | x | 29 | 29 | RPC Service User | /var/lib/nfs | /sbin/nologin |
| 30 | nfsnobody | x | 65534 | 65534 | Anonymous NFS User | /var/lib/nfs | /sbin/nologin |
| 31 | setroubleshoot | x | 993 | 990 | | /var/lib/setroubleshoot | /sbin/nologin |
| 32 | pulse | x | 171 | 171 | PulseAudio System Daemon | /var/run/pulse | /sbin/nologin |
| 33 | gdm | x | 42 | 42 | | /var/lib/gdm | /sbin/nologin |
| 34 | gnome-initial-setup | x | 992 | 987 | | /run/gnome-initial-setup/ | /sbin/nologin |
| 35 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
| 36 | avahi | x | 70 | 70 | Avahi mDNS/DNS-SD Stack | /var/run/avahi-daemon | /sbin/nologin |
| 37 | postfix | x | 89 | 89 | | /var/spool/postfix | /sbin/nologin |
| 38 | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin |
| 39 | tcpdump | x | 72 | 72 | | / | /sbin/nologin |
| 40 | lisi | x | 1000 | 1000 | lisi | /home/lisi | /bin/bash |
| 41 | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false |
+—-+———————+——+——-+——-+—————————————————————–+—————————+—————-+

案例：将userdb库user表中uid小于100的前10条记录导出，存为/myload/user2.txt文件
mysql> select * from user where uid<100 limit 10;
+—-+———-+——+——+——+———-+—————–+—————-+
| id | username | pass | uid | gid | comment | homedir | shell |
+—-+———-+——+——+——+———-+—————–+—————-+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| 5 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| 6 | sync | x | 5 | 0 | sync | /sbin | /bin/sync |
| 7 | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
| 8 | halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
| 9 | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
| 10 | operator | x | 11 | 0 | operator | /root | /sbin/nologin |
+—-+———-+——+——+——+———-+—————–+—————-+
10 rows in set (0.00 sec)

mysql> select * from user where uid<100 limit 10 into outfile “/myload/select.txt” fields terminated by “:” lines terminated by “\n”;
Query OK, 10 rows affected (0.00 sec)

到文件去确认一下
[root@50 ~]# cat /myload/select.txt
1:root:x:0:0:root:/root:/bin/bash
2:bin:x:1:1:bin:/bin:/sbin/nologin
3:daemon:x:2:2:daemon:/sbin:/sbin/nologin
4:adm:x:3:4:adm:/var/adm:/sbin/nologin
5:lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
6:sync:x:5:0:sync:/sbin:/bin/sync
7:shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
8:halt:x:7:0:halt:/sbin:/sbin/halt
9:mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
10:operator:x:11:0:operator:/root:/sbin/nologin

插入到制定的字段
mysql> load data infile ‘/myload/123’ into table tea8 fields terminated by ‘:’ lines terminated by ‘\n’ (id,age,name);
Query OK, 3 rows affected (0.10 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
确认一下
mysql> select * from tea8;
+—-+—–+——–+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——–+———+————-+
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
| 5 | 22 | tarena | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep |
| 7 | 21 | jimmy | boy | eat,it,girl |
| 8 | 16 | jimmy | boy | sleep,girl |
| 9 | 35 | kitty | girl | eat,sleep |
| 10 | 26 | po | girl | NULL |
| 11 | 19 | lipo | girl | NULL |
| 12 | 32 | pily | girl | NULL |
+—-+—–+——–+———+————-+
11 rows in set (0.00 sec)

管理表记录：增删改查
先创建个表，并适当修改一下
mysql> create table tea7(id int(5) not null auto_increment,name char(10) not null,index(id),primary key(id));
Query OK, 0 rows affected (0.31 sec)

mysql> show create table tea7;
+——-+————————————————————————————————————————————————————————-+
| Table | Create Table |
+——-+————————————————————————————————————————————————————————-+
| tea7 | CREATE TABLE `tea7` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`name` char(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+——-+————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)

mysql> desc tea7;
+——-+———-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+———-+——+—–+———+—————-+
| id | int(5) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
+——-+———-+——+—–+———+—————-+
2 rows in set (0.00 sec)

mysql> alter table tea7 add(sex enum(“boy”,”girl”,”secrect”) default “girl”,hobby set(“eat”,”sleep”,”game”,”it”,”girl”));
Query OK, 0 rows affected (0.52 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc tea7;
+——-+—————————————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+—————————————+——+—–+———+—————-+
| id | int(5) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| sex | enum(‘boy’,’girl’,’secrect’) | YES | | girl | |
| hobby | set(‘eat’,’sleep’,’game’,’it’,’girl’) | YES | | NULL | |
+——-+—————————————+——+—–+———+—————-+
4 rows in set (0.00 sec)

插入值
格式 insert into 表名（字段名列表） values（字段值列表）
不制定字段则插入全部字段

字段值与字段类型必须匹配
字符型字段，要使用单引或双印括起来
依次给所有字段赋值时，字段名可以省略
只给部分字段赋值，必须写明对应的字段名
mysql> desc tea7;
+——-+—————————————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+—————————————+——+—–+———+—————-+
| id | int(5) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| sex | enum(‘boy’,’girl’,’secrect’) | YES | | girl | |
| hobby | set(‘eat’,’sleep’,’game’,’it’,’girl’) | YES | | NULL | |
+——-+—————————————+——+—–+———+—————-+
4 rows in set (0.01 sec)

mysql> insert into tea7(name,sex,hobby) values(“lucy”,”girl”,”game”);
Query OK, 1 row affected (0.08 sec)

mysql> select * from tea7;
+—-+——+——+——-+
| id | name | sex | hobby |
+—-+——+——+——-+
| 1 | lucy | girl | game |
+—-+——+——+——-+
1 row in set (0.00 sec)

mysql> insert into tea7(name,hobby) values(“lily”,”sleep,eat”);
Query OK, 1 row affected (0.34 sec)

mysql> select * from tea7;
+—-+——+——+———–+
| id | name | sex | hobby |
+—-+——+——+———–+
| 1 | lucy | girl | game |
| 2 | lily | girl | eat,sleep |
+—-+——+——+———–+
2 rows in set (0.00 sec)

mysql> insert into tea7(name,hobby) values(“lily”,”sleep,eat,drunk”);
ERROR 1265 (01000): Data truncated for column ‘hobby’ at row 1

更新值
方法一：把整个字段全部更新
mysql> update tea7 set sex=”secrect”;
Query OK, 3 rows affected (0.15 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from tea7;
+—-+——+———+————-+
| id | name | sex | hobby |
+—-+——+———+————-+
| 1 | lucy | secrect | game |
| 2 | lily | secrect | eat,sleep |
| 3 | bob | secrect | eat,it,girl |
+—-+——+———+————-+
3 rows in set (0.00 sec)

方法二，只更新一部分
mysql> update tea7 set sex=”girl” where name=”lucy”;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from tea7;
+—-+——+———+————-+
| id | name | sex | hobby |
+—-+——+———+————-+
| 1 | lucy | girl | game |
| 2 | lily | secrect | eat,sleep |
| 3 | bob | secrect | eat,it,girl |
+—-+——+———+————-+
3 rows in set (0.00 sec)

删除值
方法一：只删除制定的字段
mysql> delete from tea7 where name=”lily”;
Query OK, 1 row affected (0.07 sec)

mysql> select * from tea7;
+—-+——+———+————-+
| id | name | sex | hobby |
+—-+——+———+————-+
| 1 | lucy | girl | game |
| 3 | bob | secrect | eat,it,girl |
+—-+——+———+————-+
2 rows in set (0.00 sec)

方法二：删除所有的列
delete from 表名;

查询
匹配条件
字段类型
等于= 大于或大于等于 > >= 小于或小于等于<,<= 不等于!=

字符类型
等于= 不等于!= 匹配空IS NULL 匹配非空IS NOT NULL

逻辑判断
逻辑或or 逻辑与and 逻辑非! 提高优先级()

范围内匹配/去重显示
在…范围内in (值列表) 不在…范围内not in (值列表) 在…之间between 数字1 and 数字2 去重显示distinct 字段名

增加1个字段，插入一些值
mysql> alter table tea7 add age int(3) not null default 18 after id;
Query OK, 0 rows affected (0.54 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc tea7;
+——-+—————————————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+—————————————+——+—–+———+—————-+
| id | int(5) | NO | PRI | NULL | auto_increment |
| age | int(3) | NO | | 18 | |
| name | char(10) | NO | | NULL | |
| sex | enum(‘boy’,’girl’,’secrect’) | YES | | girl | |
| hobby | set(‘eat’,’sleep’,’game’,’it’,’girl’) | YES | | NULL | |
+——-+—————————————+——+—–+———+—————-+
5 rows in set (0.00 sec)

mysql> select * from tea7;
+—-+—–+——+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——+———+————-+
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
+—-+—–+——+———+————-+
3 rows in set (0.00 sec)

mysql> insert into tea7(age,name,sex,hobby) values(22,”tarena”,”girl”,”eat,sleep”),(25,”kitty”,”girl”,”sleep”),(21,”jimmy
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from tea7;
+—-+—–+——–+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——–+———+————-+
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
| 5 | 22 | tarena | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep |
| 7 | 21 | jimmy | boy | eat,it,girl |
+—-+—–+——–+———+————-+
6 rows in set (0.00 sec)

验证范围内匹配
mysql> select * from tea7;
+—-+—–+——–+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——–+———+————-+
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
| 5 | 22 | tarena | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep |
| 7 | 21 | jimmy | boy | eat,it,girl |
+—-+—–+——–+———+————-+
6 rows in set (0.00 sec)

mysql> select * from tea7 where age in (18,22);
+—-+—–+——–+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——–+———+————-+
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
| 5 | 22 | tarena | girl | eat,sleep |
+—-+—–+——–+———+————-+
4 rows in set (0.00 sec)

mysql> select * from tea7 where age not in (18,22);
+—-+—–+——-+——+————-+
| id | age | name | sex | hobby |
+—-+—–+——-+——+————-+
| 6 | 25 | kitty | girl | sleep |
| 7 | 21 | jimmy | boy | eat,it,girl |
+—-+—–+——-+——+————-+
2 rows in set (0.00 sec)

mysql> select * from tea7 where age between 22 and 25;
+—-+—–+——–+——+———–+
| id | age | name | sex | hobby |
+—-+—–+——–+——+———–+
| 5 | 22 | tarena | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep |
+—-+—–+——–+——+———–+
2 rows in set (0.00 sec)

去重较为复杂，以下为简单用法
6,distinct与聚集函数:

Mysql5.0.3以及之后的版本，聚集函数和distinct可以搭配使用，比如:

1:对所有的行执行计算，指定all参数或不给参数（all是默认所有行为，不需要指定，如果不指定distinct，则假定为all）；
2:只包含不同的值，指定distinct参数；
3:如果指定列名，则distinct只能用于count（）；distinct不能用于count（*），因此不允许使用count（distinct）；distinct必须使用列名，不能用于计算或者表达式；

SELECT avg(distinct age) as id FROM person WHERE age = 20;
这条SQL语句中，使用avg（）函数返回vend列中vend_id=1003的对应的price平均价格，因为使用了distinct参数，因此平均值只考虑不同的值（唯一值）

先统计一下tea7一共多少行
mysql> select count(*) as count from tea7 ;
+——-+
| count |
+——-+
| 6 |
+——-+
1 row in set (0.00 sec)

然后把年龄去重，看看有多少行
mysql> select count(distinct age) as count_distinct from tea7 ;
+—————-+
| count_distinct |
+—————-+
| 4 |
+—————-+
1 row in set (0.00 sec)

字符比较验证
mysql> select * from tea7 where age!=18;
+—-+—–+——–+——+————-+
| id | age | name | sex | hobby |
+—-+—–+——–+——+————-+
| 5 | 22 | tarena | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep |
| 7 | 21 | jimmy | boy | eat,it,girl |
+—-+—–+——–+——+————-+
3 rows in set (0.00 sec)

mysql> select * from tea7 where age=18;
+—-+—–+——+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——+———+————-+
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
+—-+—–+——+———+————-+
3 rows in set (0.00 sec)

高级匹配条件
where 字段名 like ‘通配符’ _匹配单个字符 %匹配任意多个字符
mysql> select * from tea7 where name like ‘lu_’;
Empty set (0.00 sec)

mysql> select * from tea7 where name like ‘luc_’;
+—-+—–+——+——+——-+
| id | age | name | sex | hobby |
+—-+—–+——+——+——-+
| 1 | 18 | lucy | girl | game |
+—-+—–+——+——+——-+
1 row in set (0.00 sec)

mysql> select * from tea7 where name like ‘l%’;
+—-+—–+——+——+——–+
| id | age | name | sex | hobby |
+—-+—–+——+——+——–+
| 1 | 18 | lucy | girl | game |
| 4 | 18 | lily | girl | eat,it |
+—-+—–+——+——+——–+
2 rows in set (0.00 sec)

regexp匹配正则表达式
以j或者l开头
mysql> select * from tea7 where name regexp ‘^j|l’;
+—-+—–+——-+——+————-+
| id | age | name | sex | hobby |
+—-+—–+——-+——+————-+
| 1 | 18 | lucy | girl | game |
| 4 | 18 | lily | girl | eat,it |
| 7 | 21 | jimmy | boy | eat,it,girl |
+—-+—–+——-+——+————-+
3 rows in set (0.00 sec)

以j开头或者b结尾
mysql> select * from tea7 where name regexp ‘^j|b$’;
+—-+—–+——-+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——-+———+————-+
| 3 | 18 | bob | secrect | eat,it,girl |
| 7 | 21 | jimmy | boy | eat,it,girl |
+—-+—–+——-+———+————-+
2 rows in set (0.00 sec)

四则运算
加法+ 减法- 乘法* 除法/ 求模%

查询结果操作
聚集函数
平均值avg(字段名) 求和sum 最小值min 最大值max 统计个数count
接上面的例子，统计一下，以j开头或者b结尾的记录个数
mysql> select * from tea7 where name regexp ‘^j|b$’;
+—-+—–+——-+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——-+———+————-+
| 3 | 18 | bob | secrect | eat,it,girl |
| 7 | 21 | jimmy | boy | eat,it,girl |
+—-+—–+——-+———+————-+
2 rows in set (0.00 sec)

mysql> select count(*) from tea7 where name regexp ‘^j|b$’;
+———-+
| count(*) |
+———-+
| 2 |
+———-+
1 row in set (0.00 sec)

统计下所有人、女孩、男孩的平均年龄
mysql> select avg(age) from tea7;
+———-+
| avg(age) |
+———-+
| 20.3333 |
+———-+
1 row in set (0.00 sec)

mysql> select avg(age) from tea7 where sex=”girl”;
+———-+
| avg(age) |
+———-+
| 20.7500 |
+———-+
1 row in set (0.00 sec)

mysql> select avg(age) from tea7 where sex=”boy”;
+———-+
| avg(age) |
+———-+
| 21.0000 |
+———-+
1 row in set (0.00 sec)

查询并排序，默认是升序
mysql> select * from tea7 order by age;
+—-+—–+——–+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——–+———+————-+
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
| 7 | 21 | jimmy | boy | eat,it,girl |
| 5 | 22 | tarena | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep |
+—-+—–+——–+———+————-+
6 rows in set (0.00 sec)

升序排列
mysql> select * from tea7 order by age asc;
+—-+—–+——–+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——–+———+————-+
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
| 7 | 21 | jimmy | boy | eat,it,girl |
| 5 | 22 | tarena | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep |
+—-+—–+——–+———+————-+
6 rows in set (0.00 sec)

降序排列
mysql> select * from tea7 order by age desc;
+—-+—–+——–+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——–+———+————-+
| 6 | 25 | kitty | girl | sleep |
| 5 | 22 | tarena | girl | eat,sleep |
| 7 | 21 | jimmy | boy | eat,it,girl |
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
+—-+—–+——–+———+————-+
6 rows in set (0.00 sec)

查询结果分组

查询一下表里各个年龄段的人数
mysql> select age,count(age) as num from tea7 group by age;
+—–+—–+
| age | num |
+—–+—–+
| 18 | 3 |
| 21 | 1 |
| 22 | 1 |
| 25 | 1 |
+—–+—–+
4 rows in set (0.01 sec)

having可以代替where过滤分组数据
先重新建一张表
mysql> desc tea7;
+——-+—————————————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+—————————————+——+—–+———+—————-+
| id | int(5) | NO | PRI | NULL | auto_increment |
| age | int(3) | NO | | 18 | |
| name | char(10) | NO | | NULL | |
| sex | enum(‘boy’,’girl’,’secrect’) | YES | | girl | |
| hobby | set(‘eat’,’sleep’,’game’,’it’,’girl’) | YES | | NULL | |
+——-+—————————————+——+—–+———+—————-+
5 rows in set (0.01 sec)

mysql> insert into tea7(age,name,sex,hobby) values(16,”jimmy”,”boy”,”sleep,girl”);
Query OK, 1 row affected (0.08 sec)

mysql> insert into tea7(age,name,sex,hobby) values(35,”kitty”,”girl”,”sleep,eat”);
Query OK, 1 row affected (0.12 sec)

mysql> select * from tea7;
+—-+—–+——–+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——–+———+————-+
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
| 5 | 22 | tarena | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep |
| 7 | 21 | jimmy | boy | eat,it,girl |
| 8 | 16 | jimmy | boy | sleep,girl |
| 9 | 35 | kitty | girl | eat,sleep |
+—-+—–+——–+———+————-+
8 rows in set (0.00 sec)

统计下同名人的个数，并列出来
mysql> select name,count(*) as num from tea7 group by name having count(*) >= 2;
+——-+—–+
| name | num |
+——-+—–+
| jimmy | 2 |
| kitty | 2 |
+——-+—–+
2 rows in set (0.00 sec)