15个MySQL常用基本SQL语句

在学习SQL语句之前，首先需要区分几个概念，我们常说的数据库是指数据库软件，例如MySQL、Oracle、SQL Server等，而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。

在MySQL中，数据库称为database，数据表称为table，一个数据库软件中有多个数据库（databases），每个数据库中又可以有多个数据表（tables），最终，数据是存储在数据表中。

数据库和数据表之间的关系可以用下面这个图来表示，对于一个数据库来说，有多个数据表。

在正式开始写SQL语句之前，需要说明两点。

这里通过MySQL Workbench来写SQL代码，在Workbench中，执行一条SQL语句的方式有两种。

了解了这之后，接下来介绍一些常见的命令，分两部分：数据库常用命令和数据表常用命令。

1、查看有哪些数据库

2、创建数据库

创建一个名为Testdb的数据库。

3、创建数据库并指定编码格式

有些时候，为了防止中文乱码，创建数据库的时候需要指定编码格式。

4、使用某个数据库

使用mydb这个数据库，或者进入mydb这个数据库。

5、删除数据库

删除Testdb这个数据库。

1、查看有哪些数据表

进入某个数据库之后，想查看有哪些数据表，SQL语句为：

mydb是一个新建的数据库，所以自然是没有数据表。

2、创建数据表

建表SQL语句格式为：

说明： 每个字段以逗号分隔，最后一个字段不加逗号。

例如，给定一个学员信息表，如下表所示。

根据以上表格，建表SQL语句如下。

以上语句中，primary key表示主键，意思是这个字段作为记录的唯一标识，就像每个人的身份z号，都是唯一确定的。

3、查看表结构

查看表结构的SQL命令为：

执行该命令会显示表stuinfo的基本结构，例如有哪些字段，每个字段是什么类型，谁是主键等。

4、修改数据表

修改数据表通过drop子句进行，比如，建完表后，想增加一个字段，SQL语句的格式为：

想在指定位置增加一个字段，例如，在某个字段后增加一个字段，SQL语句的格式为：

如果在某个字段之前增加字段，用before即可。

例如，在字段age后增加一个字段major（专业），SQL语句为：

执行这个命令，再通过describe查看表结构，会发现表中多了一个字段major。

如果要删除major这个字段，通过drop子句，SQL语句为：

5、重命名表

重命名表通过alter+rename来实现，SQL语句格式为：

这里为了不影响之前创建的表，我们创建一个新表，SQL语句如下。

以上创建一个名为stuInfoTest的表，现在想将它的名称改成stuinfotest1，SQL语句为：

6、删除数据表

删除数据表通过drop进行，SQL语句格式为：

例如，删除数据表stuinfotest1，SQL语句为：

7、插入记录

此时的表stuinfo是空的，没有数据，我们要向表中插入记录。

插入记录通过insert into进行，SQL语句格式为：

例如，向表stuinfo插入一条记录，SQL语句为：

注意：上方是一条SQL语句，为了可读性换行，记住一条SQL语句默认以分号结尾。

如果需要一次性插入多条记录，SQL语句格式为：

例如，向表stuinfo再插入两条记录，SQL语句为：

注意：如果设置了主键，插入记录的主键字段是不能重复的，也就是不能插入重复的记录。

作业：大家可以按照上述方法将上面的学员信息表中的所有记录都插入表stuinfo中。

8、查询记录

有了数据之后，就可以查询记录了，查询记录通过select子句进行。

例如，想查询表stuinfo中的所有记录，SQL语句为：

执行之后，就可以看到表stuinfo中的所有记录了。

如果想查询符合某个条件的记录，就要用到where子句了，SQL格式为：

例如，想查询stuid为20161001的记录，SQL语句为：

9、删除记录

删除记录通过delete子句进行，SQL语句格式为：

例如，想删除stuid为20161002的记录，SQL语句为：

10、修改记录

修改记录通过update子句进行，update就是更新的意思，SQL语句格式为：

例如，想将学号（stuid）为20161001的记录的姓名（stuname）更新为Jack，SQL语句为：

以上，就是MySQL中的基本SQL语句。

零基础如何学习数据分析？查看下方专栏。

create database test_dbuse test_dbcreate table student(id int auto_increment not null,name varchar(20),class varchar(20),primary key(id))create table course(id int auto_increment not null,name varchar(20),primary key(id))create table takes(id int auto_increment not null,takes_student int,takes_course int,score int(2),primary key(id))alter table takes add constraint fk_takes_student foreign key (takes_student) references student (id) on delete set null on update cascadealter table takes add constraint fk_takes_course foreign key (takes_course) references course (id) on delete set null on update cascade

insert into 表名 values(default,"名字","2011-04-15 12:22:25") //default可以换成null

------------------------------

insert into 表名 (name,datetime) values("名字","2011-04-15 12:22:25")

使用SQL语法大写，增加可读性(小写部分就是自己数据库写的表/字段喽，具体你懂得...)。

创建数据库：CREATE DATABASE mysql_db

删除数据库：DROP DATABASE mysql_db

查看数据库：SHOW DATABASES

使用数据库：USE mysql_db

查看数据库中的表:SHOW TABLES

创建表：

CREATE TABLE user(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(20) UNIQUE NOT NULL,

sex ENUM('1','2','3') NOT NULL DEFAULT 3

)

查看表结构：①SELECT COLUMN FROM user

②DESC user

查看表创建语句(可查看各种自动生成的名字)：SHOW CREATE TABLE user

插入表记录：

①INSERT INTO user(id,username,sex) VALUES(1,'Tom',1)

②INSERT user(username) VALUES ('John')

查找表：SELECT * FROM user

删除表中的数据(保留表结构)：TRUNCATE TABLE user

将查询结果写入新表:

INSERT [INTO] privinces(sex) SELECT sex FROM user GROUP BY sex

(若表中有数据，请不要随意更改表名、列名)

修改表名：

①ALTER TABLE user RENAME [AS|TO] users

②RENAME TABLE user TO users

添加单列：

ALTER TABLE user

ADD [COLUMN] age SMALLINT NOT NULL UNSIGNED DEFUALT 18

[FIRST | AFTER sex]

删除列：ALTER TABLE user DROP sex[,DROP age]

修改列名称和定义：

ALTER TABLE user

CHANGE [COLUMN] age a_ge TINYINT NOT NULL UNSIGNED AFTER id

单表更新：UPDATE user SET age = age + 5,sex = 1 [WHERE id = 2]

单表删除：DELETE FROM user [WHERE id =3]

单(多)表连接：

SELECT p.userid,p.username FROM user AS p LEFT JOIN user AS s

ON p.userid = s.age

单(多)表删除：

DELETE * FROM user [AS] u1 LEFT JOIN (

SELECT p.userid FROM user AS p LEFT JOIN user AS s

ON p.userid =s.age GROUP BY p.userid HAVING COUNT(s.age)>1 ) [AS] u2

ON u1.userid = u2.age

WHERE u1.userid >u2.userid

查找记录：

SELETE select_expr [,select_expr]

[

FROM table_references

[WHERE where_condition]

[GROUP BY {col_name|position}[ASC|DESC],...] //查询结果分组

[HAVING where_condition] //设置分组条件

[ORDER BY {col_name|expr|position}[ASC|DESC]] //对结果排序

LIMIT {[offset,]row_count|row_count OFFSET offset}]//限制记录数量

]

记录为2、3行：SELECT * FROM user LIMIT 1,2

别名使用(在项目中一般都使用别名)AS alias_name:

SELECT id AS uid FROM user AS u

子查询：

SELECT AVG(age) FROM user WHERE userid =

[ALL|SOME|ANY|[NOT] IN|[NOT] EXISTS](SELECT uid FROM privinces WHERE pname = '河南')

外键约束：

(父表为user表,必须先在父表中插入数据，才能在子表中插入数据）

CASEADE：从父表中删除或更新行 且 自动级联删除或更新子表中匹配的行；

SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL

RESTRICT:拒绝对父表的更新或删除 *** 作；

CREATE TABLE privinces(

pid SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

pname VARCHAR(20) NOT NULL ,

uid SMALLINT,

FOREIGN KEY (uid) REFERENCES user (id)//privinces的字段uid与user表的字段id外键约束

ON DELETE CASEADE //删除时 级联删除

)

增加主键、唯一、外键约束：

ALTER TABLE privinces

ADD [CONSTRAINT uq_pname] UNIQUE(pname)

添加/删除默认约束：

ALTER TABLE privinces

ALTER pname {SET DEFAULT literal | DROP DEFAULT}

删除主键约束：

ALTER TABLE privinces DROP PRIMARY KEY

删除唯一(索引)约束：

（查看约束名字key_name: SHOW INDEXS FROM privinces）

ALTER TABLE privinces DROP {INDEX | KEY} 约束名

删除外键约束：

（查看约束名字：SHOW CREATE TABLE privinces）

ALTER TABLE privinces DROP FOREIGN KEY 约束名

多表更新：

UPDATE 表的参照关系 SET col_name = {[expr | value]}

表的参照关系：

tab1_reference {[INNER|CROSS] JOIN |{LEFT|RIGHT}[OUTER] JOIN} tab2_refenence ON conditional_expr

内连接：INNER JOIN

左外连接：LEFT JOIN

右外连接：ROGHT JOIN

UPDATE user INNER JOIN privinces ON user_privince = pname

SET user_privince = pid//(更新user内连接privince,条件是 user_privince.=pname,设置user_privince=pid)

创建数据表的同时将查询结果写入数据表：

CREATE TABLE table_name

[create_definition, ...] //创建语句的定义

select_statement //要插入查询结果的 查询语句

字符函数：

CONCAT()字符连接；CONCAT('I','LOVE','YOU')

CONCAT_WS()使用指定的分隔符进行字符连接；

CONCAT_WS('|','A','B')第一个为分割符

FORMAT()数字格式化

FORMAT(99999.99,1)将数字格式化，并保留1位小数

LOWER()转换成小写字符

UPPER()转换成大写字符

LEFT()获取左侧字符

RIGHT()获取右侧字符

LENGTH()获取字符长度；

LTRIN()删除前导字符；

RTRIM()删除后续字符

TRIM()删除前导、后续字符；

SUBSTRING()字符串的截取；

SUBSTRING('MYSQL',1,2)从第一位中截取2位；（mysql不允许为负值）

[NOT] LIKE模式匹配

SELECT * FROM user WHERE username LIKE '%1%%' ESCAPE '1'

REPLACE()替换字符串中的字符

REPLACE('M??Y??SQL','?','*')将M??Y??SQL中的？替换成*

数值运算符：

CEIL()进一去整；

DIV整数除法；

FLOOR()舍一去整；

MOD取余数（模）；

POWER()幂运算；

ROUND()四舍五入；ROUND(3.562,1)取一位小数

TRUNCATE()数字截取；TRUNCATE(125.89,1)小数后截取一位；

日期时间：

NOW()当前时间；

CURDATE()当前日期；

CURTIME()当前时间；

DATE_ADD()日期变化；

DATE_ADD('2015-6-23',INTERVAL 365 DAY)

DATEDIFF()俩日期之间的差值；

DATE_FORMAT()进行日期格式化；

SELECT DATE_FORMAT('2015-6-26','%m/%d/%Y')

分类: mysql

---