MySQL DQL语句

查询语句

查询表的所有数据行
命令
    select <字段1>,<字段n> from <表名> where <表达式>
其中 select from where 是不能随表改的，是关键字，支持大小写

查看test中的所有数据
'''
mysql> insert into test values(1,'scott'),(2,'scot'),(4,'sco'),(3,'scott_boy'),(5,'kaka');

mysql> use scott;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| stud            |
| student         |
| test            |
+-----------------+
3 rows in set (0.00 sec)

mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | scott     |
|  2 | scot      |
|  3 | scott_boy |
|  4 | sco       |
|  5 | kaka      |
+----+-----------+
5 rows in set (0.00 sec)

mysql> select id, name from test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | scott     |
|  2 | scot      |
|  3 | scott_boy |
|  4 | sco       |
|  5 | kaka      |
+----+-----------+
5 rows in set (0.00 sec)

查询两个
mysql> select id, name from test limit 2;
+----+-------+
| id | name  |
+----+-------+
|  1 | scott |
|  2 | scot  |
+----+-------+
2 rows in set (0.00 sec)

按条件查询
提示：字符串查询要加引号
mysql> select id, name from test where id =1;
+----+-------+
| id | name  |
+----+-------+
|  1 | scott |
+----+-------+
1 row in set (0.00 sec)

mysql> select id, name from test where name =scot;
ERROR 1054 (42S22): Unknown column 'scot' in 'where clause'
mysql> select id, name from test where name ='scot';
+----+------+
| id | name |
+----+------+
|  2 | scot |
+----+------+
1 row in set (0.00 sec)

#  and 符号
mysql> select id, name from test where name ='scot' and id = 2;
+----+------+
| id | name |
+----+------+
|  2 | scot |
+----+------+
1 row in set (0.00 sec)

mysql> select id, name from test where name ='scot' and id = 1;
Empty set (0.00 sec)

# or 符号
mysql> select id, name from test where name ='scot' or id = 1;
+----+-------+
| id | name  |
+----+-------+
|  1 | scott |
|  2 | scot  |
+----+-------+
2 rows in set (0.01 sec)


'''

按照范围查询

'''
mysql> select id, name from test where id>2 or id <1;
+----+-----------+
| id | name      |
+----+-----------+
|  3 | scott_boy |
|  4 | sco       |
|  5 | kaka      |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select id, name from test where id>2 or id <4;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | scott     |
|  2 | scot      |
|  3 | scott_boy |
|  4 | sco       |
|  5 | kaka      |

'''

排序
'''
默认按照升序排序
mysql> select id, name from test order by id;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | scott     |
|  2 | scot      |
|  3 | scott_boy |
|  4 | sco       |
|  5 | kaka      |
+----+-----------+
5 rows in set (0.00 sec)
# 升序
mysql> select id, name from test order by id asc;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | scott     |
|  2 | scot      |
|  3 | scott_boy |
|  4 | sco       |
|  5 | kaka      |
+----+-----------+
5 rows in set (0.00 sec)
# 倒序
mysql> select id, name from test order by id desc;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | kaka      |
|  4 | sco       |
|  3 | scott_boy |
|  2 | scot      |
|  1 | scott     |
+----+-----------+
5 rows in set (0.00 sec)'''

查看执行过程
关注 rows: 5

mysql> explain select * from test where name='scot'\G

'''
mysql> explain select * from test where name='scot'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where
1 row in set (0.00 sec)

mysql> create index index_name on test(name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   | MUL | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> explain select * from test where name='scot'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref
possible_keys: index_name
          key: index_name
      key_len: 60
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

'''

   修改表中指定条件固定列的数据
1. 命令语法 update 表名 set 字段=新值 ... where 条件（一定要注意条件）

增量恢复mysql数据
编辑/etc/my.conf
开启binlog功能
重启服务
[root@mangodb ~]# mysqlbinlog /data/mysql/mysqlbin_scott.000001

'''
mysql> insert into test values(6,'xiaozhang');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  3 | gongli    |
|  5 | ka        |
|  4 | sco       |
|  2 | scot      |
|  1 | scott     |
|  6 | xiaozhang |
+----+-----------+
6 rows in set (0.00 sec)

切割mysqlbinlog
binlog 只记得更改 不记得查询
[root@mangodb mysql]# mysqladmin -uroot -pddbddb flush-log

[root@mangodb mysql]# mysql -uroot -pddbddb scott < /root/scott.sql
[root@mangodb mysql]# mysql -uroot -pddbddb -e "select * from scott.test"
+----+-----------+
| id | name      |
+----+-----------+
|  1 | scott     |
|  2 | scot      |
|  3 | scott_boy |
|  4 | sco       |
|  5 | kaka      |
+----+-----------+

+----+-----------+
[root@mangodb mysql]# mysqlbinlog -d scott mysqlbin_scott.000001 > bin.sql

[root@mangodb mysql]# ls
bin.sql      ib_logfile1  mysql                  mysqlbin_scott.index  scott_gbk
ibdata1      mangodb.err  mysqlbin_scott.000001  performance_schema    test
ib_logfile0  mangodb.pid  mysqlbin_scott.000002  scott

[root@mangodb mysql]# vim bin.sql
[root@mangodb mysql]# mysql -uroot -pddbddb scott < bin.sql

mysql> select * from  scott.test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | scott     |
|  2 | scot      |
|  3 | scott_boy |
|  4 | sco       |
|  5 | kaka      |
|  6 | xiaozhang |
+----+-----------+

'''

防止误操作
[root@mangodb mysql]# alias mysql='mysql -U'
[root@mangodb mysql]# alias

删除表中的数据

1.命令 delete from 表名 where 表达式
'''
mysql> delete from test where id>1

mysql> delete from test where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | scot      |
|  3 | scott_boy |
|  4 | sco       |
|  5 | kaka      |
|  6 | xiaozhang |
+----+-----------+
5 rows in set (0.00 sec)

清空数据库
truncate table test;
'''

增删改表的字段
1.命令 alter table 表名 add 字段 类型 其他
2.测试表数据
show create table test\G
'''
mysql> alter table test add sex char(4);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+-----------+------+
| id | name      | sex  |
+----+-----------+------+
|  2 | scot      | NULL |
|  3 | scott_boy | NULL |
|  4 | sco       | NULL |
|  5 | kaka      | NULL |
|  6 | xiaozhang | NULL |
+----+-----------+------+

插在name后面
mysql> alter table test add age int(4) after name;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| age   | int(4)   | YES  |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

没有插在谁的前面的命令
只有first
mysql> alter table test add qq varchar(15) first;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+----+-----------+------+------+
| qq   | id | name      | age  | sex  |
+------+----+-----------+------+------+
| NULL |  2 | scot      | NULL | NULL |
| NULL |  3 | scott_boy | NULL | NULL |
| NULL |  4 | sco       | NULL | NULL |
| NULL |  5 | kaka      | NULL | NULL |
| NULL |  6 | xiaozhang | NULL | NULL |
+------+----+-----------+------+------+
5 rows in set (0.00 sec)
删除字段
mysql> alter table test drop sex;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   | MUL | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

'''

rename 用法

rename table 表原名 to 表新名
'''
mysql> rename table test scott;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'scott' at line 1
mysql> rename table test to scott;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables ;
+-----------------+
| Tables_in_scott |
+-----------------+
| SC              |
| course          |
| scott           |
| stud            |
| student         |
+-----------------+
5 rows in set (0.00 sec)

mysql> rename table scott to test;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table test rename to scott;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables ;
+-----------------+
| Tables_in_scott |
+-----------------+
| SC              |
| course          |
| scott           |
| stud            |
| student         |
+-----------------+