常用SQL语句汇总

命令行下操作和SQL图形化操作结果相同。

将mysql/bin添加到环境变量

1.Cmd登录：(也可以从mysql自带的命令行登录)

>mysql -h localhost -u root -p

输入密码

登录成功界面如下：

2.创建及删除数据库

创建数据库

> CREATE DATABASE student;

选择数据库

> USE student;

 

这种方式等价于在cmd直接用

> mysql -h localhost -u root -p student

 

查看数据库：

>show databases;

删除数据库：

>DROP DATABASE aaa;

3.创建，删除表

创建表的一个例子：

  > create table class1(

  -> ID INT NOT NULL,

  -> name VARCHAR(10) NOT NULL,

  -> age INT NOT NULL,

  -> birthday DATE

  -> );

 

SQL允许指定默认值，在插入行时如果不给出值，DBMS将自动采用默认值。默认值在CREATE TABLE语句的列定义中用关键字DEFAULT指定。如中间有个关键字：

>score INT NOT NULL  DEFALUT 1,

显示表（显示具体信息可在后面加表名）

>SHOW TABLES;

+------------------+

| Tables_in_animal |

+------------------+

| class1           |

+------------------+

1 row in set (0.00 sec)

 

查看表的类型： 

>DESCRIBE class1;

返回结果：

+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| ID       | int(11)     | YES  |     | NULL    |       |

| name     | varchar(10) | YES  |     | NULL    |       |

| age      | int(11)     | YES  |     | NULL    |       |

| birthday | date        | YES  |     | NULL    |       |

+----------+-------------+------+-----+---------+-------+

4 rows in set (0.03 sec)

删除表：

>DROP TABLE `new_tb`

4.更新表

使用ALTER TABLE语句可以更新表定义。

增加一列：

->ALTER TABLE class1
>ADD score INT(3) NOT NULL DEFAULT 80;

删除一列：

->ALTER TABLE class1
>DROP COLUMN score;

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
1. 用新的列布局创建一个新表；
2. 使用INSERT SELECT语句（关于这条语句的详细介绍，请参阅第15课）从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
3. 检验包含所需数据的新表；
4. 重命名旧表（如果确定，可以删除它）；
5. 用旧表原来的名字重命名新表；
6. 根据需要，重新创建触发器、存储过程、索引和外键。

5.在表中插入数据：

1>.INSERT插入

INSERT INTO 表名称 VALUES (值1, 值2,....)

> INSERT INTO class1

-> VALUES ('1', 'DDD', '55', '1999-1-1'),

       ('6', 'BBB', '41', '1998-5-3'),

       ('5', 'rrr', '26', '1991-4-6'),

       ('7', 'ttt', '27', '1989-5-26');

在指定列中插入：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

>INSERT INTO class (id, name, age)

->VALUES ('8', 'III',15);

2>.插入检出的数据

先有一张表class2

->INSERT INTO class1

>SELECT *

>FROM class2;

class2里面的内容被插入class1中了

3>.txt文本插入

在本地创建一个txt，每行包含一个记录，用定位符(tab)把值分开，并且以CREATE TABLE语句中列出的列次序给出。对于丢失的值用NULL表示

> LOAD DATA LOCAL INFILE 'k:aaa.txt' INTO TABLE class1

-> LINES TERMINATED BY '\r\n';

6.更新记录

当中间的一些记录错了可以用UPDATE进行修改

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

>UPDATE class1 

SET age = 17 WHERE id = 1;

如果要改多行：

  

>UPDATE class1 

SET age = 17,

    name = 'MMM'

WHERE id = 1;

  

如果不加where则认为更新所有行

7.删除记录

DELETE FROM 表名称 WHERE 列名称 = 值

>DELETE FROM class1 WHERE id = 8;

不加where为删除全部数据，但如果想从表中删除数据，最好不要使用DELETE。可使用TRUNCATE TABLE语句，因为这条语句的数据更快。

DELETE语句从表中删除行数据。但是，DELETE不删除表本身。

8.查询语句

select语法：

 SELECT 显示的列
 FROM 表
 WHERE 查询满足的条件 
 GROUP BY 分类
 ORDER BY 排序

主要用到的是以上三种格式，后面三个为可选项

> SELECT * FROM class1;

上述表中查询全部结果：

+------+------+------+------------+
| id   | name | age  | birthday   |
+------+------+------+------------+
|    1 | ddd  |   17 | 1999-01-01 |
|    2 | BBB  |   20 | 1997-05-03 |
|    3 | EEE  |   18 | 1999-01-13 |
|    4 | PPP  |   23 | 1993-12-12 |
|    7 | ttt  |   27 | 1989-05-26 |
|    5 | rrr  |   25 | 1991-04-06 |
|    6 | bbb  |   18 | 1998-05-03 |
+------+------+------+------------+

显示年龄大于30的ID和姓名：

>SELECT id, name as '姓名'

->FROM class1 WHERE age>=26;

+------+------+
| id   | 姓名 |
+------+------+
|    1 | ddd  |
|    7 | ttt  |
|    5 | rrr  |
|    6 | bbb  |
+------+------+

select后面跟显示的名字，用as（别名）或者空格，比如 name '姓名'可以对该列进行重命名

where后面包含条件：可以使用AND,OR，括号()改变优先级，进行组合条件。 AND比OR具有更高优先级

                    BETWEENT需要和AND连用它的上下限。 

            ：对于NULL，必须要用IS NOT NULL或者IS NULL来比较。*注意在GROUP BY中，两个NULL值视为相同。

            ：模式匹配 LIKE 或 NOT LIKE比较操作符。模式匹配的其他类型使用拓展正则表达式（详见正则表达式）

            ：IN几个选项中选一个

DISTINCT检索唯一的记录

>SELECT DISTINCT age FROM class1;

+------+
| age  |
+------+
|   17 |
|   20 |
|   18 |
|   23 |
|   27 |
|   25 |
+------+

排序

asc表升序，desc或（-）表降序

也可排多个顺序，将第二第三个顺序依次加到ORDER后面。

> SELECT * FROM class1 ORDER BY birthday desc;

+------+------+------+------------+
| id   | name | age  | birthday   |
+------+------+------+------------+
|    3 | EEE  |   18 | 1999-01-13 |
|    1 | ddd  |   17 | 1999-01-01 |
|    6 | bbb  |   18 | 1998-05-03 |
|    2 | BBB  |   20 | 1997-05-03 |
|    4 | PPP  |   23 | 1993-12-12 |
|    5 | rrr  |   25 | 1991-04-06 |
|    7 | ttt  |   27 | 1989-05-26 |
+------+------+------+------------+

分组和HAVING：

HAVING用来过滤分组，HAVING的用法类似于WHERE。

->SELECT age, COUNT(*)
>FROM class1
>GROUP BY age
>HAVING age < 25;

9.联结多个表

普通连接多个表：

在FROM子句列出信息来源表，并用逗号隔开。
当从多个表组合(联结)信息时，你需要指定一个表中的记录怎样能匹配其它表的记录。用where子句关联起两张表就可以实现。

这种方式也可以联结多个表，但不要联结不必要的表。联结的表越多，则性能下降越多。

几种JOIN

先建2张表：（表无意义）

表:teacher

表:class1

内联结（Inner Join）

>SELECT * 
->FROM class1 INNER JOIN teacher ON class1.id = teacher.id_t;

结果相当于连个表的交集部分

指定RIGHT或LEFT的外联结

LEFT JOIN：

>SELECT * 
->FROM class1 LEFT JOIN teacher ON class1.id = teacher.id_t;

结果类似于左边那个表有的记录，就把右边表的记录全联结起来，如果右边没有就用NULL代替

RIGHT JOIN：

>SELECT * 
->FROM class1 RIGHT JOIN teacher ON class1.id = teacher.id_t;

全外连接（FULL JOIN）

Mysql不支持FULL JOIN= =

效果相当于两个表的并集

网上copy的一段：

一般要使得数据库查询语句性能好点遵循一下原则:
在做表与表的连接查询时，大表在前，小表在后
不使用表别名，通过字段前缀区分不同表中的字段
查询条件中的限制条件要写在表连接条件前
尽量使用索引的字段做为查询条件

10.组合查询（复合查询）

把多个SELECT查询回来的结果合并作为一个查询结果集返回，这种查询方式成为组合查询。

但查询结果必须有相同的列，比如下面这样就会报错

->SELECT * FROM class1 WHERE id = 1
>UNION
>SELECT * FROM teacher WHERE id_t = 2;

[Err] 1222 - The used SELECT statements have a different number of columns

10.函数

拼接字段：

->SELECT name, CONCAT('age:',age,', birthday:',birthday) as 'age infomation'
>from class1;

聚集函数：

COUNT(*)计算行数：

> SELECT age, COUNT(*) FROM class1 GROUP BY age;
+------+----------+
| age  | COUNT(*) |
+------+----------+
|   17 |        1 |
|   18 |        2 |
|   20 |        1 |
|   23 |        1 |
|   25 |        1 |
|   27 |        1 |
+------+----------+
6 rows in set (0.00 sec)

RTRIM()去掉字符串右边的空格，LTRIM()去掉字符串左边的空格，TRIM()去掉字符串左右两边的空格

函数主要分为:

文本处理函数，日期处理函数，数值处理函数。用到自己去查

11.视图

视图是虚拟的表。与包含数据的表不一样，视图不包含任何列或数据，只包含使用时动态检索数据的查询。

在添加或更改这些表中的数据时，视图将返回改变过的数据。

创建视图：

->CREATE VIEW stu_view AS
>SELECT *
>from class1
>where age >= 23;

使用视图：

->SELECT *
>from stu_view;

删除视图，可以使用DROP语句:

->DROP VIEW stu_view

覆盖（或更新）视图，必须先删除，再创建。