SQL语句练习

 

 

 

第一部分：SQL分类

详细介绍：https://www.cnblogs.com/fan-yuan/p/7879353.html

四种常见的SQL语句

?
1
2
3
4
DDL：数据定义语言      ###GRANT/COMMIT/ROLLBACK...
DCL：数据控制语言      ###CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
DML：数据操作语言      ###insert/update/delete
DQL：数据的查询语言    ### select * from mysql.user

 

数据查询语言DQL

?
1
2
3
4
5
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

 

数据操纵语言DML





?
1
2
3
4
数据操纵语言DML主要有三种形式：
 1) 插入：INSERT
 2) 更新：UPDATE
 3) 删除：DELETE




 


数据定义语言DDL




?
1
2
3
4
5
6
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
 索引、同义词、聚簇等如：
 CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
 |  |    |    |    |
 表 视图 索引 同义词 簇
 DDL操作是隐性提交的！不能rollback 




 


数据控制语言DCL


 数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
 1) GRANT：授权。
 ​
 2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
 回滚---ROLLBACK
 回滚命令使数据库状态回到上次最后提交的状态。其格式为：
 SQL>ROLLBACK;
 ​
 3) COMMIT [WORK]：提交。
 ​
     在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。
 提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。
 ​
 (1) 显式提交
 用COMMIT命令直接完成的提交为显式提交。其格式为：
 SQL>COMMIT；
 ​
 (2) 隐式提交
 用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
 ​
 (3) 自动提交
 若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：
 SQL>SET AUTOCOMMIT ON；


 


数据定义语言


DDL的应用：


    

  • 

    库名不能有大写字母   
    

    • 

  • 

    建库要加字符集 
    

    • 

  • 

    库名不能有数字开头
    

    • 

  • 

    库名要和业务相关
    

    • 





?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
3306 [share]>create database test;
Query OK, 1 row affected (0.00 sec)
​
3306 [share]>create schema page;
Query OK, 1 row affected (0.00 sec)
​
3306 [share]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| page               |
| performance_schema |
| share              |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)




 


建库标准语句




?
1
2
3
4
5
6
7
8
9
10
3306 [share]>create database db charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
​
3306 [share]>show create database test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)




 


删除(生产中禁止使用)


 3306 [share]>drop database test;
 Query OK, 0 rows affected (0.00 sec)


修改




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
3306 [share]>create database nsh;
Query OK, 1 row affected (0.00 sec)
​
3306 [share]>show create database nsh;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| nsh      | CREATE DATABASE `nsh` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
​
3306 [share]>alter database nsh charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
​
3306 [share]>show create database nsh;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| nsh      | CREATE DATABASE `nsh` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)




 


 查询库相关信息（DQL）


 创建表格：
 use 库名
 create table  xuesheng (
 列1  属性（数据类型、约束、其他属性） ，
 列2  属性，
 列3  属性 
 )


实例讲解




?
1
2
3
4
5
6
7
8
CREATE TABLE students (
id INT PRIMARY KEY NOT NULL  AUTO_INCREMENT COMMENT '学生学号',
sname VARCHAR(64) NOT NULL COMMENT '学生姓名',
xingbie ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '学生性别',
shouji  CHAR(11) UNIQUE KEY NOT NULL COMMENT '手机号',
age  TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '学生年龄',
ruxue TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4;




 


 建表规范：
 1. 表名小写
 2. 不能是数字开头
 3. 注意字符集和存储引擎
 4. 表名和业务有关
 5. 选择合适的数据类型
 6. 每个列都要有注释
 7. 每个列设置为非空，无法保证非空，用0来填充。


删除(生产中禁用次命令)




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
3306 [nsh]>drop tables students;
 Query OK, 0 rows affected (0.01 sec)
修改；
 3306 [nsh]>alter table students add qq varchar(64) unique key not null  comment 'qq号';
 Query OK, 0 rows affected (0.02 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 ​
 3306 [nsh]>desc students;
 +---------+---------------------+------+-----+-------------------+----------------+
 | Field   | Type                | Null | Key | Default           | Extra          |
 +---------+---------------------+------+-----+-------------------+----------------+
 | id      | int(11)             | NO   | PRI | NULL              | auto_increment |
 | sname   | varchar(64)         | NO   |     | NULL              |                |
 | xingbie | enum('m','f','n')   | NO   |     | n                 |                |
 | shouji  | char(11)            | NO   | UNI | NULL              |                |
 | age     | tinyint(3) unsigned | NO   |     | 0                 |                |
 | ruxue   | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
 | qq      | varchar(64)         | NO   | UNI | NULL              |                |
 +---------+---------------------+------+-----+-------------------+----------------+
 7 rows in set (0.00 sec)​




在sname后面加微信列




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
3306 [nsh]>alter table students add weixin varchar(64) unique key not null comment '微信' after sname;
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
3306 [nsh]>desc students;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| id      | int(11)             | NO   | PRI | NULL              | auto_increment |
| sname   | varchar(64)         | NO   |     | NULL              |                |
| weixin  | varchar(64)         | NO   | UNI | NULL              |                |
| xingbie | enum('m','f','n')   | NO   |     | n                 |                |
| shouji  | char(11)            | NO   | UNI | NULL              |                |
| age     | tinyint(3) unsigned | NO   |     | 0                 |                |
| ruxue   | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
| qq      | varchar(64)         | NO   | UNI | NULL              |                |
+---------+---------------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)




 


在id列前面加一个新列num




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
3306 [nsh]>alter table students add num int first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
3306 [nsh]>desc students;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| num     | int(11)             | YES  |     | NULL              |                |
| id      | int(11)             | NO   | PRI | NULL              | auto_increment |
| sname   | varchar(64)         | NO   |     | NULL              |                |
| weixin  | varchar(64)         | NO   | UNI | NULL              |                |
| xingbie | enum('m','f','n')   | NO   |     | n                 |                |
| shouji  | char(11)            | NO   | UNI | NULL              |                |
| age     | tinyint(3) unsigned | NO   |     | 0                 |                |
| ruxue   | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
| qq      | varchar(64)         | NO   | UNI | NULL              |                |
+---------+---------------------+------+-----+-------------------+----------------+
9 rows in set (0.00 sec)




 


将添加的列删除




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
3306 [nsh]>alter table students drop num;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
3306 [nsh]>alter table students drop weixin;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
3306 [nsh]>alter table students drop qq;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
3306 [nsh]>desc students;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| id      | int(11)             | NO   | PRI | NULL              | auto_increment |
| sname   | varchar(64)         | NO   |     | NULL              |                |
| xingbie | enum('m','f','n')   | NO   |     | n                 |                |
| shouji  | char(11)            | NO   | UNI | NULL              |                |
| age     | tinyint(3) unsigned | NO   |     | 0                 |                |
| ruxue   | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+---------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)




 


修改sname数据类型的属性




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
3306 [nsh]>alter  table students modify sname varchar(32) not null comment 'rpename';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
3306 [nsh]>desc students;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| id      | int(11)             | NO   | PRI | NULL              | auto_increment |
| sname   | varchar(32)         | NO   |     | NULL              |                |
| xingbie | enum('m','f','n')   | NO   |     | n                 |                |
| shouji  | char(11)            | NO   | UNI | NULL              |                |
| age     | tinyint(3) unsigned | NO   |     | 0                 |                |
| ruxue   | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+---------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)​




将sname 改为sn 数据类型改为 varchar(64)




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
3306 [nsh]>alter table students change sname sn varchar(64);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
3306 [nsh]>desc students;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| id      | int(11)             | NO   | PRI | NULL              | auto_increment |
| sn      | varchar(64)         | YES  |     | NULL              |                |
| xingbie | enum('m','f','n')   | NO   |     | n                 |                |
| shouji  | char(11)            | NO   | UNI | NULL              |                |
| age     | tinyint(3) unsigned | NO   |     | 0                 |                |
| ruxue   | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+---------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)　




表属性查询（DQL）




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
3306 [nsh]>show create table students;
 +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table    | Create Table                                                                                                                  |
 +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | students | CREATE TABLE `students` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
   `sn` varchar(64) DEFAULT NULL,
   `xingbie` enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '学生性别',
   `shouji` char(11) NOT NULL COMMENT '手机号',
   `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '学生年龄',
   `ruxue` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
   PRIMARY KEY (`id`),
   UNIQUE KEY `shouji` (`shouji`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4                    |
 +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 ​
 3306 [nsh]>select * from students;
 Empty set (0.00 sec)
 ​
 3306 [nsh]>select * from students where id < 5;
 Empty set (0.00 sec)​




DML应用


 作用： 对表中的数据行进行增、删、改


insert




?
1
2
3
4
5
6
7
8
9
10
3306 [nsh]>insert into students(sn,xingbie,shouji,age) values('zs','m','12345678901','18');
Query OK, 1 row affected (0.00 sec)
​
3306 [nsh]>select * from students;
+----+------+---------+-------------+-----+---------------------+
| id | sn   | xingbie | shouji      | age | ruxue               |
+----+------+---------+-------------+-----+---------------------+
|  1 | zs   | m       | 12345678901 |  18 | 2019-04-22 23:54:17 |
+----+------+---------+-------------+-----+---------------------+
1 row in set (0.00 sec) ​​




-- 省事的插入方法




?
1
2
3
4
5
6
7
8
9
10
11
3306 [nsh]>insert into students values(2,'ls','f','98765432109','19',now()); ###now（）是？？？？
Query OK, 1 row affected (0.01 sec)
​
3306 [nsh]>select * from students;
+----+------+---------+-------------+-----+---------------------+
| id | sn   | xingbie | shouji      | age | ruxue               |
+----+------+---------+-------------+-----+---------------------+
|  1 | zs   | m       | 12345678901 |  18 | 2019-04-22 23:54:17 |
|  2 | ls   | f       | 98765432109 |  19 | 2019-04-22 23:56:04 |
+----+------+---------+-------------+-----+---------------------+
2 rows in set (0.00 sec)​




一次性录入多行数据




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
3306 [nsh]>INSERT INTO students(sn,xingbie,shouji,age)
    ->
    -> VALUES
    ->
    -> ('w5','f','120',19),
    ->
    -> ('m6','m','119',20),
    ->
    -> ('m66','f','1190',27);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
​
3306 [nsh]>select * from students;
+----+------+---------+-------------+-----+---------------------+
| id | sn   | xingbie | shouji      | age | ruxue               |
+----+------+---------+-------------+-----+---------------------+
|  1 | zs   | m       | 12345678901 |  18 | 2019-04-22 23:54:17 |
|  2 | ls   | f       | 98765432109 |  19 | 2019-04-22 23:56:04 |
|  3 | lss  | m       | 98765432179 |  19 | 2019-04-22 23:58:38 |
|  4 | w5   | f       | 120         |  19 | 2019-04-23 00:00:19 |
|  5 | m6   | m       | 119         |  20 | 2019-04-23 00:00:19 |
|  6 | m66  | f       | 1190        |  27 | 2019-04-23 00:00:19 |
+----+------+---------+-------------+-----+---------------------+
6 rows in set (0.00 sec)
​
3306 [nsh]>insert into students(sn,shouji,age) values('wze','1299999','17');
Query OK, 1 row affected (0.01 sec)
​
3306 [nsh]>select * from students;
+----+------+---------+-------------+-----+---------------------+
| id | sn   | xingbie | shouji      | age | ruxue               |
+----+------+---------+-------------+-----+---------------------+
|  1 | zs   | m       | 12345678901 |  18 | 2019-04-22 23:54:17 |
|  2 | ls   | f       | 98765432109 |  19 | 2019-04-22 23:56:04 |
|  3 | lss  | m       | 98765432179 |  19 | 2019-04-22 23:58:38 |
|  4 | w5   | f       | 120         |  19 | 2019-04-23 00:00:19 |
|  5 | m6   | m       | 119         |  20 | 2019-04-23 00:00:19 |
|  6 | m66  | f       | 1190        |  27 | 2019-04-23 00:00:19 |
|  7 | wze  | n       | 1299999     |  17 | 2019-04-23 00:01:15 |
+----+------+---------+-------------+-----+---------------------+
7 rows in set (0.00 sec)　




update




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
3306 [nsh]>update students set sn='nsh' where id=6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
​
3306 [nsh]>select * from students;
+----+------+---------+-------------+-----+---------------------+
| id | sn   | xingbie | shouji      | age | ruxue               |
+----+------+---------+-------------+-----+---------------------+
|  1 | zs   | m       | 12345678901 |  18 | 2019-04-22 23:54:17 |
|  2 | ls   | f       | 98765432109 |  19 | 2019-04-22 23:56:04 |
|  3 | lss  | m       | 98765432179 |  19 | 2019-04-22 23:58:38 |
|  4 | w5   | f       | 120         |  19 | 2019-04-23 00:00:19 |
|  5 | m6   | m       | 119         |  20 | 2019-04-23 00:00:19 |
|  6 | nsh  | f       | 1190        |  27 | 2019-04-23 00:00:19 |
|  7 | w55  | n       | 1200        |  17 | 2019-04-23 00:01:15 |
+----+------+---------+-------------+-----+---------------------+
7 rows in set (0.00 sec)　




注意：update语句必须要加where。


delete（危险！！）




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
3306 [nsh]>delete from students where id=7;
Query OK, 1 row affected (0.00 sec)
​
3306 [nsh]>select * from students;
+----+------+---------+-------------+-----+---------------------+
| id | sn   | xingbie | shouji      | age | ruxue               |
+----+------+---------+-------------+-----+---------------------+
|  1 | zs   | m       | 12345678901 |  18 | 2019-04-22 23:54:17 |
|  2 | ls   | f       | 98765432109 |  19 | 2019-04-22 23:56:04 |
|  3 | lss  | m       | 98765432179 |  19 | 2019-04-22 23:58:38 |
|  4 | w5   | f       | 120         |  19 | 2019-04-23 00:00:19 |
|  5 | m6   | m       | 119         |  20 | 2019-04-23 00:00:19 |
|  6 | nsh  | f       | 1190        |  27 | 2019-04-23 00:00:19 |
+----+------+---------+-------------+-----+---------------------+
6 rows in set (0.00 sec)




 


伪删除：用update来替代delete，最终保证业务中查不到（select）即可


 




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
添加状态列
3306 [nsh]>alter table students add state tinyint not null default 1;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
使用update替代delete
3306 [nsh]>update students set state=0 where id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
​
3306 [nsh]>select & from students;
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 '& from students' at line 1
3306 [nsh]>select * from students;
+----+------+---------+-------------+-----+---------------------+-------+
| id | sn   | xingbie | shouji      | age | ruxue               | state |
+----+------+---------+-------------+-----+---------------------+-------+
|  1 | zs   | m       | 12345678901 |  18 | 2019-04-22 23:54:17 |     1 |
|  2 | ls   | f       | 98765432109 |  19 | 2019-04-22 23:56:04 |     1 |
|  3 | lss  | m       | 98765432179 |  19 | 2019-04-22 23:58:38 |     1 |
|  4 | w5   | f       | 120         |  19 | 2019-04-23 00:00:19 |     1 |
|  5 | m6   | m       | 119         |  20 | 2019-04-23 00:00:19 |     1 |
|  6 | nsh  | f       | 1190        |  27 | 2019-04-23 00:00:19 |     0 |
+----+------+---------+-------------+-----+---------------------+-------+
6 rows in set (0.00 sec)
​
业务查询时 ，加入状态判断
3306 [nsh]>select * from students where state=1;
+----+------+---------+-------------+-----+---------------------+-------+
| id | sn   | xingbie | shouji      | age | ruxue               | state |
+----+------+---------+-------------+-----+---------------------+-------+
|  1 | zs   | m       | 12345678901 |  18 | 2019-04-22 23:54:17 |     1 |
|  2 | ls   | f       | 98765432109 |  19 | 2019-04-22 23:56:04 |     1 |
|  3 | lss  | m       | 98765432179 |  19 | 2019-04-22 23:58:38 |     1 |
|  4 | w5   | f       | 120         |  19 | 2019-04-23 00:00:19 |     1 |
|  5 | m6   | m       | 119         |  20 | 2019-04-23 00:00:19 |     1 |
+----+------+---------+-------------+-----+---------------------+-------+
5 rows in set (0.00 sec)​




DQL应用(select )




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
查看mysql的参数设定情况
3306 [nsh]>select @@basedir;
+-------------+
| @@basedir   |
+-------------+
| /app/mysql/ |
+-------------+
1 row in set (0.00 sec)
​
3306 [nsh]>select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)
​
调用mysql的内置函数：
3306 [nsh]>select database();
+------------+
| database() |
+------------+
| nsh        |
+------------+
1 row in set (0.00 sec)
​
3306 [nsh]>select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
​
3306 [nsh]>select now();
+---------------------+
| now()               |
+---------------------+
| 2019-04-23 00:11:44 |
+---------------------+
1 row in set (0.00 sec)
​
3306 [nsh]>select concat("this is a test page !!!");
+-----------------------------------+
| concat("this is a test page !!!") |
+-----------------------------------+
| this is a test page !!!           |
+-----------------------------------+
1 row in set (0.00 sec)​




from 子句


语法：


 select  列1,列2...  from 表
 select * from 表；


 


 oldguo带大家学单词：
 world ===>世界
 city    ===>城市
 country ===>国家
 countrylanguage ===>国家语言
 city ===>城市
 DESC city;
 ID :城市ID
 NAME :城市名
 CountryCode :国家代码，比如中国CHN 美国USA
 District :区域
 Population :人口


将world.sql导入到数据库




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
查询中国所有的城市
3306 [world]>DESC city;
3306 [world]>SELECT * FROM city WHERE countrycode='CHN';
查询CH开头国家代号的城市信息
3306 [world]>SELECT * FROM city WHERE countrycode LIKE 'CH%';  #  注意:%不能加在前面，例如:'%CH%'
查看中国和美国的城市信息
3306 [world]>SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
3306 [world]>SELECT * FROM city WHERE countrycode IN ('CHN','USA');
3306 [world]>SELECT * FROM city WHERE countrycode='CHN'
UNION ALL    ###注意: 一般我们会将 or或者in 语句改写成union union all 语句
SELECT * FROM city WHERE countrycode='USA' 
查询中国城市中人口数量大于500w的城市信息
3306 [world]>SELECT * FROM city WHERE countrycode='CHN' AND population>5000000
查询中国城市中人口数量小于9w的信息
3306 [world]>SELECT * FROM city WHERE countrycode='CHN' AND population <90000;
查询一下世界上小于100人口的城市
3306 [world]>SELECT * FROM city WHERE population<100;
查询人口数100w到200w之前的城市信息
3306 [world]>SELECT * FROM city WHERE population>1000000 AND population <2000000
3306 [world]>SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;




 


group by +常用聚合函数


作用：根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列


常用聚合函数


 max() ：最大值
 min() ：最小值
 avg() ：平均值
 sum() ：总和
 count() ：个数


GROUP BY + 聚合函数公式


 1.遇到统计想函数
 2.形容词前 GROUP BY
 3.函数中央是名词
 4.列名select后添加


实例说明：


统计世界上每个国家的总人口数.






?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
3306 [world]>use world
 Database changed
 3306 [world]>SELECT countrycode ,SUM(population) FROM city GROUP BY countrycode;
 +-------------+-----------------+
 | countrycode | SUM(population) |
 +-------------+-----------------+
 | ABW         |           29034 |
 | AFG         |         2332100 |
 | AGO         |         2561600 |
 | AIA         |            1556 |
 | ALB         |          270000 |
 .................................
 | YUG         |         2189507 |
 | ZAF         |        15196370 |
 | ZMB         |         2473500 |
 | ZWE         |         2730420 |
 +-------------+-----------------+
 232 rows in set (0.00 sec)




 


统计中国各个省的总人口数量(练习)




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
3306 [world]>SELECT district,SUM(Population) FROM city WHERE countrycode='chn' GROUP BY district;
+----------------+-----------------+
| district       | SUM(Population) |
+----------------+-----------------+
| Anhui          |         5141136 |
| Chongqing      |         6351600 |
| Fujian         |         3575650 |
| Gansu          |         2462631 |
....................................
| Tibet          |          120000 |
| Xinxiang       |         2894705 |
| Yunnan         |         2451016 |
| Zhejiang       |         5807384 |
+----------------+-----------------+
31 rows in set (0.00 sec)　




统计世界上每个国家的城市数量(练习)






?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
3306 [world]>SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;
 +-------------+-----------+
 | countrycode | COUNT(id) |
 +-------------+-----------+
 | ABW         |         1 |
 | AFG         |         4 |
 | AGO         |         5 |
 | AIA         |         2 |
 | ALB         |         1 |
 ...........................
 | YEM         |         6 |
 | YUG         |         8 |
 | ZAF         |        44 |
 | ZMB         |         7 |
 | ZWE         |         6 |
 +-------------+-----------+
 232 rows in set (0.01 sec)




 


having


where|group|having


统计中国每个省的总人口数，将总人口数小于100w




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
3306 [world]>SELECT district,SUM(Population)
     -> FROM city
     -> WHERE countrycode='chn'
     -> GROUP BY district
     -> HAVING SUM(Population) < 1000000 ;
 +----------+-----------------+
 | district | SUM(Population) |
 +----------+-----------------+
 | Hainan   |          557120 |
 | Ningxia  |          802362 |
 | Qinghai  |          700200 |
 | Tibet    |          120000 |
 +----------+-----------------+
 4 rows in set (0.00 sec)




　　


 


相关本节说明


oldguo简书地址


https://www.jianshu.com/p/08c4b78402ff


sql92.99标准






            

            
posted @ 
 
大风歌兮 
阅读(674) 
评论(0) 
编辑 
收藏 
举报


        

        
        




    
    

    
    



    

        
    

        

            
                
                
            
        

    

    

    

    



    



            

        


        
        

    

    

    




    

        










    


    
    








  

  
点击右上角即可分享

  微信分享提示