mysql语句类型

mysql语句

DDL CREATE：创建
DROP：删除
ALTER：修改

DML INSERT：向表中插入数据
DELETE：删除表中数据
UPDATE：更新表中数据
SELECT：查询表中数据

DCL GRANT：授权
REVOKE：移除授权

在mysql中，我们用增，删，改，查，来表述mysql中的语句用法

mysql> show character set;  //查看支持所有字符集
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |

mysql> show databases;  //查看数据库。
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database zdj;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;   //这里可以看到已经创建了一个数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zdj                |
+--------------------+
5 rows in set (0.00 sec)

//创建了一个名为student的表，参数如下。

mysql> create table student (id int not null primary key auto_increment,name varchar(100),age tinyint);
Query OK, 0 rows affected (0.09 sec)

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

增 INSERT

//添加表中的数据如下

mysql> insert student(name,age) value('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangsan',26),('zhangsan',20),('lisi',null),('chenshuo',10),( 'wangwu',3),('quiyi',15),('qiuxiaotian',20);
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

查 SELECT

mysql> select * from student;   //查看这个表中的数据
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | quiyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

//以别名的方式显示表的内容，只是查看，没有更改，也不能更改

mysql> select name as n,age as a from student;
+-------------+------+
| n           | a    |
+-------------+------+
| tom         |   20 |
| jerry       |   23 |
| wangqing    |   25 |
| sean        |   28 |
| zhangsan    |   26 |
| zhangsan    |   20 |
| lisi        |   50 |
| chenshuo    |   10 |
| wangwu      |  100 |
| quiyi       |   15 |
| qiuxiaotian |   20 |
+-------------+------+
11 rows in set (0.00 sec)


//可以中文，也可以英文

mysql> select name as 姓名,age as 年龄 from student;
+-------------+--------+
| 姓名        | 年龄   |
+-------------+--------+
| tom         |     20 |
| jerry       |     23 |
| wangqing    |     25 |
| sean        |     28 |
| zhangsan    |     26 |
| zhangsan    |     20 |
| lisi        |     50 |
| chenshuo    |     10 |
| wangwu      |    100 |
| quiyi       |     15 |
| qiuxiaotian |     20 |
+-------------+--------+
11 rows in set (0.00 sec)

//查看表中大于20岁的记录

mysql> select * from student where age >20;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | wangqing |   25 |
|  4 | sean     |   28 |
|  5 | zhangsan |   26 |
|  7 | lisi     |   50 |
|  9 | wangwu   |  100 |
+----+----------+------+
6 rows in set (0.00 sec)

查询表中名字叫zhangsan且年龄大于20的记录

mysql> select * from student where age >20 and name = 'zhangsan';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | zhangsan |   26 |
+----+----------+------+
1 row in set (0.00 sec)

查询表中年龄最大的四位同学

mysql> select * from student order by age limit 7,4;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | zhangsan |   26 |
|  4 | sean     |   28 |
|  7 | lisi     |   50 |
|  9 | wangwu   |  100 |
+----+----------+------+
4 rows in set (0.00 sec)

//以age字段降序排序

mysql> select * from student order by age desc;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  9 | wangwu      |  100 |
|  7 | lisi        |   50 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  6 | zhangsan    |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | quiyi       |   15 |
|  8 | chenshuo    |   10 |
+----+-------------+------+
11 rows in set (0.00 sec)

//查询表中名叫zhangsan的记录

mysql> select * from student where name = 'zhangsan'
    -> ;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | zhangsan |   26 |
|  6 | zhangsan |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

//查询表中年龄在23 到30 之间的记录

mysql> select * from student where age between 23 and 30;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | wangqing |   25 |
|  4 | sean     |   28 |
|  5 | zhangsan |   26 |
+----+----------+------+
4 rows in set (0.00 sec)

//查询表中年龄最小的三个跳过前两个

mysql> select * from student order by age limit 2,1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | quiyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

UPDATE 改

修改表中lisi的年龄为50 。

mysql> update student set age = 50 where id = 7;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | quiyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

//以name的方式修改wangwu的年龄为100

mysql> update student set age = 100 where name ='wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | quiyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

删 DELETE

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   16 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | quiyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

//删除表中名字叫zhangsan且年龄小于20 的记录

mysql> delete from student where age <20 and name = 'zhangsan';
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | quiyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.00 sec)

授权与取消权限

//查看授权

mysql> grant all on *.* to 'root'@'192.168.98.1' identified by 'ZHANGde12+Jun';   //授予权限
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  flush privileges;    //刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'root'@'192.168.98.1';
+------------------------------------------------------+
| Grants for root@192.168.98.1                         |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.98.1' |
+------------------------------------------------------+
1 row in set (0.00 sec)

//取消权限
mysql> revoke select on *.* from 'root'@'192.168.98.1';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'root'@'192.168.98.1';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.98.1                                                                                                                                                                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'root'@'192.168.98.1' |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)