基本sql语句

转载

wx5d0cd2f748c0c 2019-10-13 10:41:14

文章标签 sql语句 文章分类 运维

标准SQL包含了4种基本的语句类别：

DDL语句，数据定义语句，主要用来定义数据库，表名，字段，例如create,drop,alter. DML语句，数据操作语句，用来对数据记录的增删改查，还用来保证数据的一致xing。主要有select，delete，insert，update语句。 DCL语句，数据控制语句，用于控制不同数据对象访问级别的语句。定义了数据库、表、表、用户的访问权限和完全级别。常用的语句包括grant、revoke等 TCL语句，事务控制语句，用来确保事务的特xing。 CREATE TABLE建表语句在介绍建表语句之前，先简单说明一下创建数据库的语句。

创建数据库数据库创建之后，然后就是建表：

复制代码建表语句的作用就是在数据库创建一张二维表，因此在建表语句要指定每一个字段名（二维表中的列名），还有要指定对填入这些字段的数据的限制（约束条件），同时建表语句还可以指定这张表的字符集，以及之前规划好的索引等。 create table tb1( c1 int auto_increment primary key, c2 varchar(20) ); 创建了tb1表，表中有两列（两个字段）， auto_increment：指定字段c1为自增字段，mysql中一个表中只能有一个自增字段，且须为主键。 mysql> show create table tb1\G *************************** 1. row *************************** Table: tb1 Create Table: CREATE TABLE tb1 ( c1 int(11) NOT NULL AUTO_INCREMENT, c2 varchar(20) DEFAULT NULL, PRIMARY KEY (c1) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

#查看建表语句，mysql会默认选择表的存储引擎和字符集 mysql> system ls /data/mysql/mytest/tb1.* /data/mysql/mytest/tb1.frm /data/mysql/mytest/tb1.ibd #在datadir对应的目录下面会生成对应的表结构文件tb1.frm,和数据文件tb1.ibd。复制代码 MySQL支持在建表时指定temporary参数，这样创建的表是临时表，临时表是基于会话级别的表。

复制代码 #创建临时表，临时表使用show tables查不到其存在，但是可以查看表结构，也可以向临时表插入数据 mysql> create temporary table tb2(id int, info varchar(20)); Query OK, 0 rows affected (0.00 sec)

mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | tb1 | +------------------+ 1 row in set (0.00 sec)

mysql> show create table tb2; +-------+--------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------+ | tb2 | CREATE TEMPORARY TABLE tb2 ( id int(11) DEFAULT NULL, info varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

mysql> insert into tb2 values(1,"a"); Query OK, 1 row affected (0.00 sec) #临时表的表结构存在于/tmp/目录下面 mysql> show variables like "tmpdir"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | tmpdir | /tmp | +---------------+-------+ 1 row in set (0.01 sec) [root@test3 tmp]# pwd /tmp [root@test3 tmp]# ls #sql2966_99_0.frm #临时表的数据文件在MySQL5.7之后由专门的文件存储 mysql> show variables like "innodb%temp%"; +----------------------------+-----------------------+ | Variable_name | Value | +----------------------------+-----------------------+ | innodb_temp_data_file_path | ibtmp1:12M:autoextend | +----------------------------+-----------------------+ 1 row in set (0.00 sec) #临时表有专门的存储引擎 mysql> show variables like "default%tmp%"; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | default_tmp_storage_engine | InnoDB | +----------------------------+--------+ 1 row in set (0.00 sec) #临时表只对当前会话有效，当前会话断开，临时表会自动删除，在其余的会话也看不到临时表。 mysql> insert into tb2 values(1,"a"); ERROR 1146 (42S02): Table 'mytest.tb2' doesn't exist mysql> 复制代码 crate table语句还有很多参数可以使用，这些只是基本的用法，可以查看官方文档，也可以查看work bench中的介绍。

删除数据库和表复制代码 #删除数据库 mysql> drop database mytesti; Query OK, 0 rows affected (0.00 sec)

#删除表,和表结构一起删除 mysql> drop table tb1; Query OK, 0 rows affected (0.02 sec) #删除表中的所有记录，但是不删除表结构 mysql> truncate tb4; Query OK, 0 rows affected (0.04 sec)

#修改表名 // ALTER TABLE OLD_TB_NAME RENAME [TO] NEW_TB_NAME; mysql> alter table tb2 rename to tb3; Query OK, 0 rows affected (0.00 sec) // TO可以省略 mysql> alter table tb3 rename tb2; Query OK, 0 rows affected (0.00 sec)

#修改字段名（把上面的id字段名修改为user_id） ALTER TABLE TBNAME CHANGE 旧字段名新字段名约束条件； #需要注意的是这种方法不仅可以修改字段名，还可以修改字段的数据类型。 ALTER TABLE tb2 change id user_id varchar(10);

#删除字段 //ALTER TABLE 表名 DROP 字段名; ALTER TABLE tb2 DROP user_id;

#字段排序： ALTER TABLE TBNAME MODIFY 字段1 数据类型 FIRST|AFTER 字段2.

#更改表的存储引擎： ALTER TABLE 表名 engine = "存储引擎名"

#删除外键 ALTER TABLE 表名 FOREIGN KEY 外键别名。复制代码 insert插入数据 insert用于向表中插入数据。

insert插入数据 update更新数据 update更新语句一般于where条件句联合使用。

mysql> update tb2 set name = "b" where id =3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0

#如果不使用where条件语句限制，则更新表中所有的行 select查询在查询之前先导入MySQL官方提供的employeeso库数据。

下载地址：https://github.com/datacharmer/test_db/archive/master.zip

方法：直接下载zip压缩包，然后直接导入employees.sql文件即可

导入的表，各个表之间的关系如下：

单表查询

#查询可以使用*号代替表中所有的字段，也可以使用对应字段的字符，只查询出对应的要查询的字段。 #concat函数，就是连接字符串，在这里和as结合，连接了两个字段，并且重命名为full_name。复制代码 MySQL中有许多内嵌的函数可以调用，详细的函数列表参照：https://dev.mysql.com/doc/refman/5.7/en/string-functions.html 可以使用Google浏览器打开，可以翻译为中文！

单表查询和一些条件语句结合：

查看表中记录的数量

复制代码 mysql> select count() from employees; +----------+ | count() | +----------+ | 300024 | +----------+ 1 row in set (0.53 sec) 复制代码使用order by语句按照某字段排序：

order by 使用where条件语句：

where条件语句查询分组：

复制代码 #一个报错： mysql> select emp_no, dept_no from dept_manager group by dept_no; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.dept_manager.emp_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

#解决，更改sql_mode; http://www.ywnds.com/?p=8184 复制代码 group by分组 group by聚合函数，加上with rollup函数，会统计一个总的结果在最后一行。

复制代码 mysql> select count(emp_no), dept_no from dept_manager group by dept_no with rollup; +---------------+---------+ | count(emp_no) | dept_no | +---------------+---------+ | 2 | d001 | | 2 | d002 | | 2 | d003 | | 4 | d004 | | 2 | d005 | | 4 | d006 | | 2 | d007 | | 2 | d008 | | 4 | d009 | | 24 | NULL | +---------------+---------+ 10 rows in set (0.00 sec) #加上having条件语句 mysql> select count(emp_no), dept_no from dept_manager group by dept_no with rollup having dept_no > "d006"; +---------------+---------+ | count(emp_no) | dept_no | +---------------+---------+ | 2 | d007 | | 2 | d008 | | 4 | d009 | +---------------+---------+ 3 rows in set (0.00 sec)

复制代码查询某个表的数据大小以及索引大小，以及数据和索引的总大小

复制代码 USE information_schema; select b.TABLE_NAME, b.ddata, b.dindex, sum(b.ddata)+SUM(b.dindex) as total from ( SELECT TABLE_NAME, TRUNCATE (Data_length / 1024 / 1024, 2) AS ddata, TRUNCATE (INDEX_LENGTH / 1024 / 1024, 2) AS dindex FROM TABLES where TABLE_NAME = 't_hk_stock_news') as b;

联合查询

联合查询分为：内连接和外连接，其中外连接又包含左连接和右连接。

内连接：

需求1：求出经理人员的工号，姓名，xing别，部门代号（暂时不考虑部门名称）。（经理就是dept_manager中的员工）

复制代码 #使用where条件语句联合两张表查询 SELECT e.emp_no, concat( e.first_name, " ", e.last_name ), e.gender, dp.dept_no FROM employees AS e, dept_manager AS dp WHERE dp.emp_no = e.emp_no;

#采用内联合查询的方法 SELECT e.emp_no, concat( e.first_name, " ", e.last_name ), e.gender, dp.dept_no FROM employees AS e INNER JOIN dept_manager AS dp ON dp.emp_no = e.emp_no; 复制代码由上面这个查询可以体会一下的连接的含义：把两张或多张表中，相同的字段联合起来的的查询，当值相等时，就会查询出其结果。

上面的查询中，我们再加入一张表，把部门的代号换为部门名称。

复制代码 SELECT e.emp_no, concat( e.first_name, " ", e.last_name ), e.gender, dp.dept_no, dep.dept_name FROM employees AS e, dept_manager AS dp, departments AS dep WHERE dp.emp_no = e.emp_no AND dp.dept_no = dep.dept_no;

#使用inner联合查询的方式 SELECT e.emp_no, concat( e.first_name, " ", e.last_name ), e.gender, dp.dept_no, dep.dept_name FROM employees AS e INNER JOIN dept_manager AS dp ON dp.emp_no = e.emp_no INNER JOIN departments AS dep ON dp.dept_no = dep.dept_no; 复制代码外连接：外连接分为左连接和右连接，这两个连接的方式是一样的，不同的是刷选数据的方式。

语法格式如下：

SELECT 字段名 FROM 表名1 LEFT| RIGHT 表名2 ON 表名1.字段名1 = 表名2.字段名2；有以下的实例，我们来理解以下左查询和右查询：

View Code 在上面的employyes库中表关系图中，我们来做一个如下检索。

要求求出普通员工的员工号，姓名（用一个字段显示），xing别，最近的薪水，最近的一个部门，和最近的一个title。（因为在titles，salries，dept_emp表中，同一个工号员工有多条数据，因此我们可以根据时间，选择最近的员工）。

上面的需求一步一步拆解：先求出普通员工的工号，姓名，xing别和部门的代号，这个只涉及两张表的查询：

复制代码 SELECT e.emp_no, concat( e.first_name, " ", e.last_name ) AS full_name, e.gender, dp.dept_no FROM employees AS e LEFT JOIN dept_manager AS dp ON dp.emp_no = e.emp_no WHERE dp.dept_no IS NULL;

#这个表查询处理有30万条记录，因此会比较慢，暂时不考虑xing能，为了验证这个结果，可以设置where条件dp.dept_no IS NOT NULL,这样查出来的结果是经理的个人信息，恰好是24条。复制代码再求出员工的最近的薪水：

看一下薪水表的数据：

复制代码 mysql> select * from salaries where emp_no = "10002"; +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 10002 | 65828 | 1996-08-03 | 1997-08-03 | | 10002 | 65909 | 1997-08-03 | 1998-08-03 | | 10002 | 67534 | 1998-08-03 | 1999-08-03 | | 10002 | 69366 | 1999-08-03 | 2000-08-02 | | 10002 | 71963 | 2000-08-02 | 2001-08-02 | | 10002 | 72527 | 2001-08-02 | 9999-01-01 | +--------+--------+------------+------------+ 6 rows in set (0.00 sec)

#我们要查询得到的是这个表中，salary最近的那个值，也就是时间距现在最近，需要注意的是，时间最近的薪水不一定是最高的！ mysql> select emp_no, max(from_date) from salaries group by emp_no limit 4; +--------+----------------+ | emp_no | max(from_date) | +--------+----------------+ | 10001 | 2002-06-22 | | 10002 | 2001-08-02 | | 10003 | 2001-12-01 | | 10004 | 2001-11-27 | +--------+----------------+ 4 rows in set (0.11 sec)

#这样我们得出的是，每个员工的最近的from_date,然后根据这两个条件就可以求出员工最近的薪水。（这张表用的是复合索引，暂时先不提） #SQL语句如下 SELECT a.emp_no, a.salary from salaries a WHERE a.from_date = (SELECT max(b.from_date) from salaries b WHERE a.emp_no = b.emp_no GROUP BY b.emp_no)； #我们要的是员工的最近的薪水表，而不是最高的薪水表！复制代码按照上面的方法我们可以求出，员工距离现在最近的部门代号，和title。

title的SQL语句仿照上面写就可以了，但是员工的部门职称，需要再联合一张表查询，结果如下：

复制代码 SELECT a.emp_no, a.dept_no, dp.dept_name FROM dept_emp AS a, departments AS dp WHERE #WHERE条件句是一个and语句 from_date = ( SELECT #and语句的第一个条件是子查询， max(from_date) FROM dept_emp AS b WHERE a.emp_no = b.emp_no GROUP BY emp_no ) AND dp.dept_no = a.dept_no #and语句的第二个条件语句 ORDER BY a.emp_no;

#查询的结果就是每个员工的最新职称复制代码把求出的三个查询和最上面的普通员工信息的查询联合起来就是我们要得道的SQL查询：

最后的代码查询的结果执行时，可以加上limit函数，不然会很慢，至于优化问题，暂时先不管！

union联合查询

复制代码有两张表如下： mysql> select * from tb1; +----+------+ | c1 | c2 | +----+------+ | 1 | zhao | | 2 | qina | | 3 | b | | 4 | c | +----+------+ 4 rows in set (0.00 sec)

mysql> select * from tb2; +------+------+ | id | NAME | +------+------+ | 1 | a | | 3 | b | | 4 | c | | 5 | d | | 6 | e | +------+------+ 5 rows in set (0.00 sec)

#使用UNION联合查询，去重复，把重复的数值自动去掉 mysql> select * from tb1 -> union -> select * from tb2; +------+------+ | c1 | c2 | +------+------+ | 1 | zhao | | 2 | qina | | 3 | b | | 4 | c | | 1 | a | | 5 | d | | 6 | e | +------+------+ 7 rows in set (0.00 sec) #使用all关键字，会把重复的行也查询出来 mysql> select * from tb1 -> union all -> select * from tb2; +------+------+ | c1 | c2 | +------+------+ | 1 | zhao | | 2 | qina | | 3 | b | | 4 | c | | 1 | a | | 3 | b | | 4 | c | | 5 | d | | 6 | e | +------+------+ 9 rows in set (0.00 sec) 复制代码给查询的结果加上行号：

查询员工信息时，给查询的结果加上行号。

添加查询的行号 MySQL的一个排名问题：

复制代码 #有如下的数据，按照成绩排名： mysql> select * from rank; +------+-------+ | id | score | +------+-------+ | 1 | 10 | | 2 | 20 | | 3 | 20 | | 4 | 30 | | 5 | 40 | | 6 | 40 | | 7 | 20 | +------+-------+ 7 rows in set (0.00 sec) #成绩有相同的，按照成绩排名 set @prev_value = NULL; set @rank_count = 0;

SELECT id, score, CASE WHEN @prev_value = score THEN @rank_count WHEN @prev_value := score THEN @rank_count := @rank_count + 1 end as rank_column from rank ORDER BY score;

#如果我们期望有一条SQL语句完成排名，可以仿照上面的方法： mysql> SELECT id, score, -> CASE -> WHEN @prev1_value = score THEN @rank1_count -> WHEN @prev1_value := score THEN @rank1_count := @rank1_count + 1 -> end as rank_column -> from rank, (SELECT @prev1_value := NULL, @rank1_count :=0 ) as rank_column -> ORDER BY score; #注意可以测试一下加ORDER BY与不加ORDER BY的结果，会不一样的！ +------+-------+-------------+ | id | score | rank_column | +------+-------+-------------+ | 1 | 10 | 1 | | 2 | 20 | 2 | | 3 | 20 | 2 | | 7 | 20 | 2 | | 4 | 30 | 3 | | 5 | 40 | 4 | | 6 | 40 | 4 | +------+-------+-------------+ 7 rows in set (0.00 sec) SQL语句如下： SELECT id, score, CASE WHEN @prev1_value = score THEN @rank1_count WHEN @prev1_value := score THEN @rank1_count := @rank1_count + 1 end as rank_column from rank, (SELECT @prev1_value := NULL, @rank1_count :=0 ) as rank_column ORDER BY score;