江哥架构师笔记数据库还是很重要的，不管是运维还是开发，都会用到，一定要好好学习
1、在linux上安装mysql服务端
centos7安装 [root@localhost ~]#yi mariadb-server centos6安装 [root@localhost ~]#yi mysql-server [root@localhost ~]#service mysqld start [root@localhost ~]#ss -tnl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 50 *:3306 *:*
只要能正常监听3306端口就算正常启动了，这里只是使用最简单的方式进行安装启动，主要是来学习下面的mysql操作语法，其他配置文件参数配置和编译安装等操作看其他的博客。
2、基础介绍
DDL（Data Definition Languages）：数据定义语言，定义不同的数据段，常用的关键字：create、drop、alter等
DML（Data Manipulation Language）：数据操作语言，增删查改等操作，常用的关键字：insert、delete、update、select等
DCL（Data Control Language）：数据控制语言，控制各种权限和安全，常用的关键字：grant、revoke等
mysql是客户端的命令，-u后面跟用户名，没有用户名，默认root，-p后面跟要输入的密码（可以看到明文），或者只有-p按回车，在提示行中键入密码（看不到明文），因为下面是刚安装的，默认root密码为空，可以直接连接上
mysql的命令结束符：“;”或者“\g”
客户端的连接id，记录了mysql服务到目前为止的连接次数，每个新的连接会自动加1
[root@localhost ~]#mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.73 Source distribution
系统自带的数据库：
information_schema：存储系统的一些数据库对象信息，比如用户的表信息，权限信息，字符集，分区信息
mysql：存储了系统的用户权限信息
test ：测试数据库，任何人都能用
象形介绍数据库，mysql是一个数据库程序，主要是用来存储数据的，就像excel一样，里面可以存好多个数据库文件，每个数据库文件里面可以存储好多张表，每张表存储的都是一个二维数据，就像excel里面的一张表，是一样的，
下面的示例中，注意哪些是关键字，不变。哪些是变量名，要根据自己的需求改变，注意示例。
中括号中的单词是可选的，可以不写。
3、创建数据库
create database dbname;
例如：query ok：表示命令执行成功，1row affected：表示影响了一行数据，0.00表示操作执行的时间
mysql> create database test1; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | test1 | +--------------------+ 4 rows in set (0.00 sec)
4、选择数据库
use dbname;
例如：如果要操作某个数据库中的某张表，要先选择那个数据库，这里要用use命令，这里因为是刚创建的数据库，里面没有表，是空的，mysql数据库中就有很多的表
mysql> use test1; Database changed mysql> show tables; Empty set (0.00 sec) mysql> use mysql; Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log |
5、删除数据库
drop database dbname;
例如：删除test1数据库文件，删除后这个数据库就没有了，删除数据库，下面的所有的表都会被删除，所以一定要谨慎
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | test1 | +--------------------+ 4 rows in set (0.00 sec) mysql> drop database test1; Query OK, 0 rows affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec)
6、创建表
create table tablename (column_name_1 colum_type_1 constraints, column_name_2 column_type_2 constraints,... column_name_n column_type_n constraints)
说明：表名在磁盘上是以目录形式存在的，可以使用目录名允许的任何字符，column_name：列的名字，column_type：列的数据类型，contraints：列的约束条件
7、查看表的定义
desc tablename;
示例：
创建一个名为emp的表，包括ename（名）、hiredate（日期）、sal（工资）3个字段，字段类型：varchar(10)、date、int(2)
mysql> create table emp (ename varchar(10), hiredate date, sal decimal(10,2), deptno int(2)); Query OK, 0 rows affected (0.04 sec) mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
8、有时为了查看更详细的表信息，如下。
show create table tablename \G;
说明："\G"表示将表信息按列显示，有时候行显示很乱，要按照列显示更清晰
示例：可以看到这个表结构的存储引擎，字符集等
mysql> show create table emp \G; *************************** 1. row *************************** Table: emp Create Table: CREATE TABLE `emp` ( `ename` varchar(10) DEFAULT NULL, `hiredate` date DEFAULT NULL, `sal` decimal(10,2) DEFAULT NULL, `deptno` int(2) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified
9、删除表，删除操作要谨慎，要再三确认
drop table tablename; 当在库外面的时候： drop table dbname.tablename;
例如：将emp表删除
mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | emp | | event | mysql> drop table emp; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event |
10、修改表字段
alter table tablename modify [column] column_definition [first|after col_name];
说明：大多情况下，是用到修改表的操作，alter：改变的意思
示例：修改表emp的ename字段定义，将varchar（10）改为varchar（20）
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table emp modify ename varchar(20); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
11、增加表字段
alter table tablename add [column] column_defination [first | after col_name];
示例：增加age字段
mysql> alter table emp add column age int(3); #默认是补到最后一个 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | age | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table emp add column wight int(3) first; #将这个字段放到第一个 Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | wight | int(3) | YES | | NULL | | | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | age | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
12、删除表字段
alter table tablename drop [column] column_name;
示例：中括号中的内容可以省略
mysql> alter table emp drop age; Query OK, 0 rows affected (0.01 sec) mysql> alter table emp drop column wight; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
13、修改表字段名
alter table tablename change [column] old_col_name column_definition [first | after col_name];
示例：修改ename的名字包括定义
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table emp change ename hname varchar(10) first; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | hname | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
注意：change和modify都可以修改表定义，不同的是change可以修改表名，modify不可以
14、修改字段排列顺序
前面的命令字段中都有一个可选项（first | after column_name），可以修改字段在表中的位置，默认add增加的新字段在最后，change和modify默认不会修改字段的位置
例如：新增字段在ename后面
mysql> alter table emp add birth date after ename; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
例如：修改sal字段放到第一个
mysql> alter table emp modify sal date first; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | sal | date | YES | | NULL | | | ename | varchar(10) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
15、修改表名
alter table tablename rename [to] new_tablename;
例如：将表emp改名为emp1
mysql> alter table emp rename to emp1; Query OK, 0 rows affected (0.00 sec) mysql> desc emp; ERROR 1146 (42S02): Table 'mysql.emp' doesn't exist mysql> desc emp1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | sal | date | YES | | NULL | | | ename | varchar(10) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
##########下面是DML语句##########
16、插入数据
insert into tablename (filed1, filed2, ... filedn) values (value1, value2, ... valuen);
说明：也可以不用指定字段名称，但是values后面的顺序应该和字段的排列顺序一致，见下面的示例
例如：插入数据：ename
mysql> insert into emp(ename,hiredate,sal,deptno) values('zzx1','2000-01-1','2000',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp values('lisa','2003-02-1','3000',2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)
说明：如果某些字段可空，或者有默认的值，可以省略不写，可减少sql语句的复杂性
例如：因为这里没有设置默认值，没有填写的就为空
mysql> insert into emp(ename,sal) values('zzx1','2000');
Query OK, 1 row affected (0.00 sec)
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
| zzx1 | NULL | 2000.00 | NULL |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)
17、一次性插入多条记录
insert into tablename (filed1, filed2 ... filedn) values (record1_value1, record1_value2, ... record1_valuen), (record2_value1, record2_value2, ... record2_valuen) ;
说明：当插入大量的sql记录的时候，节省网络开销，提高插入效率
示例：下面这两种都可以，只要不以分号结尾，就可以继续添加，也可以都放到一行进行添加
mysql> insert into emp (ename,hiredate,sal,deptno)
-> values
-> ('zzx2','2004-0305','500',5),
-> ('zzx3','2004-03-06','5000',8)
-> ;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> insert into emp (ename,hiredate,sal,deptno) values ('zzx5','2004-06-06','5001',9),('zzx6','2004-03-07','5006',10);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
| zzx1 | NULL | 2000.00 | NULL |
| zzx2 | 0000-00-00 | 500.00 | 5 |
| zzx3 | 2004-03-06 | 5000.00 | 8 |
| zzx5 | 2004-06-06 | 5001.00 | 9 |
| zzx6 | 2004-03-07 | 5006.00 | 10 |
+-------+------------+---------+--------+
7 rows in set (0.00 sec)
18、更新记录
update tablename set filed1=value1, filed2=value2, ... filedn=valuen [where condition];
例如：将lisa薪水改为4000
mysql> update emp set sal=4000 where ename='lisa'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 4000.00 | 2 | | zzx1 | NULL | 2000.00 | NULL | | zzx2 | 0000-00-00 | 500.00 | 5 | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | | zzx6 | 2004-03-07 | 5006.00 | 10 | +-------+------------+---------+--------+ 7 rows in set (0.00 sec)
19、更新多个表中数据
update t1,t2...tn set t1.filed1=expr1, tn.filedn=exprn [where condition]
说明：多用来根据一个表中的字段，来动态更新另一个表中的字段。
例如：创建一个新表，联合查询更新数据，这里将emp简称为a，dept简称为b，如果检测到a和b的deptno相等的话，更改a中deptno相等的那一项，将a的sal修改。1,2,5项都被改过了，b表中对应的字段也被改过了。
mysql> create table dept (deptno int, deptname varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql> insert into dept values (1,'tech'),(2,'sale'),(5,'fin'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | tech | | 2 | sale | | 5 | fin | +--------+----------+ 3 rows in set (0.00 sec) mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 4000.00 | 2 | | zzx1 | NULL | 2000.00 | NULL | | zzx2 | 0000-00-00 | 500.00 | 5 | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | | zzx6 | 2004-03-07 | 5006.00 | 10 | +-------+------------+---------+--------+ 7 rows in set (0.00 sec) mysql> update emp a, dept b set a.sal=a.sal*b.deptno, b.deptname=a.ename where a.deptno=b.deptno; Query OK, 5 rows affected (0.00 sec) Rows matched: 6 Changed: 5 Warnings: 0 mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 8000.00 | 2 | | zzx1 | NULL | 2000.00 | NULL | | zzx2 | 0000-00-00 | 2500.00 | 5 | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | | zzx6 | 2004-03-07 | 5006.00 | 10 | +-------+------------+---------+--------+ 7 rows in set (0.00 sec) mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | zzx1 | | 2 | lisa | | 5 | zzx2 | +--------+----------+ 3 rows in set (0.00 sec)
20、删除记录
delete from tablename [where condition]
说明：如果某个记录不需要了，可以用delete将这条记录删除掉
例如：指定条件，就可以删除，最好在删除前先用条件过滤显示出来要删除的项，检查下。
mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 8000.00 | 2 | | zzx1 | NULL | 2000.00 | NULL | | zzx2 | 0000-00-00 | 2500.00 | 5 | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | | zzx6 | 2004-03-07 | 5006.00 | 10 | +-------+------------+---------+--------+ 7 rows in set (0.00 sec) mysql> delete from emp where deptno=10; Query OK, 1 row affected (0.00 sec) mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 8000.00 | 2 | | zzx1 | NULL | 2000.00 | NULL | | zzx2 | 0000-00-00 | 2500.00 | 5 | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | +-------+------------+---------+--------+ 6 rows in set (0.00 sec)
21、删除多个表数据
delete t1, t2, ... tn from t1, t2, ... tn [where condition];
示例：将a，b表中只要符合条件的条目都删除掉
mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 8000.00 | 2 | | zzx1 | NULL | 2000.00 | NULL | | zzx2 | 0000-00-00 | 2500.00 | 5 | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | | zzx6 | 2004-03-07 | 5006.00 | 10 | +-------+------------+---------+--------+ 7 rows in set (0.00 sec) mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | zzx1 | | 2 | lisa | | 5 | zzx2 | +--------+----------+ 3 rows in set (0.00 sec) mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=5; Query OK, 2 rows affected (0.00 sec) mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 8000.00 | 2 | | zzx1 | NULL | 2000.00 | NULL | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | +-------+------------+---------+--------+ 5 rows in set (0.00 sec) mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | zzx1 | | 2 | lisa | +--------+----------+ 2 rows in set (0.00 sec)
22、查询记录
select * from tablename [where condition];
说明：最简单的是将所有的记录全部选出，但是不常用，数据有1000w的时候，就呵呵了，一定要指定范围条件，越具体越好
例如：可以指定某些自己想要看到要显示的字段，
mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 8000.00 | 2 | | zzx1 | NULL | 2000.00 | NULL | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | +-------+------------+---------+--------+ 5 rows in set (0.00 sec) mysql> select ename,sal from emp; +-------+---------+ | ename | sal | +-------+---------+ | zzx1 | 2000.00 | | lisa | 8000.00 | | zzx1 | 2000.00 | | zzx3 | 5000.00 | | zzx5 | 5001.00 | +-------+---------+ 5 rows in set (0.00 sec)
23、条件查询
例如：查询deptno为1的记录
mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 8000.00 | 2 | | zzx1 | NULL | 2000.00 | NULL | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | +-------+------------+---------+--------+ 5 rows in set (0.00 sec) mysql> select * from emp where deptno=1; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | +-------+------------+---------+--------+ 1 row in set (0.00 sec)
说明：where后面的条件是一个字段的=比较，还可以使用>、<、>=、<=、!=等比较运算符，包括or，and等逻辑运算符
例如：
mysql> select * from emp where deptno>=2 and sal<=5000; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx3 | 2004-03-06 | 5000.00 | 8 | +-------+------------+---------+--------+ 1 row in set (0.00 sec)
24、排序
select * from tablename [where condition] [order by filed1 [desc|asc], field2 [desc|asc], ... ]
说明：指定条件后，选出来就是一批数据了，后面中括号中是根据那个字段进行排序，排序是正序还是倒序，如果根据这个字段排出来的项是一样的，这些一样的项怎么排序，根据第二个指定的字段排序，依次推，只有第一个字段相同，才会比较第二个字段。默认是正序排
例如：
mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 8000.00 | 2 | | zzx1 | NULL | 2000.00 | NULL | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | +-------+------------+---------+--------+ 5 rows in set (0.00 sec) mysql> select * from emp order by sal asc, deptno desc; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | 2000-01-01 | 2000.00 | 1 | | zzx1 | NULL | 2000.00 | NULL | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | | lisa | 2003-02-01 | 8000.00 | 2 | +-------+------------+---------+--------+ 5 rows in set (0.00 sec) mysql> select * from emp order by sal asc, deptno; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx1 | NULL | 2000.00 | NULL | | zzx1 | 2000-01-01 | 2000.00 | 1 | | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | | lisa | 2003-02-01 | 8000.00 | 2 | +-------+------------+---------+--------+ 5 rows in set (0.00 sec)
25、限制显示
select ... [limit offset_start,row_count];
说明：比如说上面的排序，如果排序后非常多，只显示几行怎么办，offset_start表示记录的起始偏移量，row_count表示显示的行数，默认起始偏移量0，只需要显示行数就可以
例如：
mysql> select * from emp order by sal asc, deptno limit 2,3; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzx3 | 2004-03-06 | 5000.00 | 8 | | zzx5 | 2004-06-06 | 5001.00 | 9 | | lisa | 2003-02-01 | 8000.00 | 2 | +-------+------------+---------+--------+ 3 rows in set (0.00 sec)
26、聚合
修改表字段null为默认为空，并保留已有的数据
alter table pod_operationrecod add column tmp varchar(100) default ""; update pod_operationrecod set tmp="" where ip is null; alter table pod_operationrecod drop ip; alter table pod_operationrecod change tmp ip varchar(100) DEFAULT '';
评论前必须登录！
注册