SQL语句
登录数据库
mysql -uroot -p
输入密码
修改mysql提示符
1.连接客户端时通过参数指定
mysql -uroot -p密码 --prompt 提示符
2.连接上客户端后，通过prompt
mysql>prompt 提示符
提示符：
\D 完整的日期
\d 当前数据库
\h 服务器名称
\u 当前用户
常用命令
SELECT VERSION();显示当前服务器版本
SELECT NOW();显示当前日期时间
SELECT USER();显示当前用户
SELECT DATABASE();显示当前数据库
查看当前服务器下的数据库
SHOW DATABASES;
创建数据库
CREATE ｛DATABASE | SCHEMA｝ [IF NOT EXITSTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
大括号必有 中括号可有可无
CREATE DATABASE t;
查看警告
SHOW WARNINGS;
修改数据库
ALTER ｛DATABASE | SCHEMA｝[db_name] [DEFAULT] CHARACTER SET [=] charset_name
删除数据库
DROP ｛DATABASE | SCHEMA｝ [IF EXITSTS] db_name
打开数据库
USE 数据库名称
创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
)
查看数据表列表
SHOW TABLES [FROM db_name]
查看数据表结构
SHOW COLUMNS FROM tb1_name
插入记录
INSERT tb1_name [(col_name,...)] {VALUES|value}(val....)
记录查找
SELECT exp，... FROM tb1_name
空值与非空
NULL NOT NULL
自动编号
AUTO_INCREMENT
主键约束
PRIMARY KEY
一张数据表只能有一个
主键保证数据的唯一性
每张数据表只能存在一个主键
主键自动为NOT NULL
唯一约束
UNIQUE KEY
每张数据表可以存在多个唯一约束
字段可以为空值
保证记录唯一性
自动编号
AUTO_INCREMENT
必须与主键同时使用
默认约束
DEFAULT
插入记录时，如果没有明确为字段赋值，则自动赋予默认值
添加单列
ALTER TABLE tb!_name ADD[COLUMN] col_name column_definition [FIRST|AFTER col_name];
添加多列
ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,...);
删除列
ALTER TABLE tb1_name DROP [COLUMN] col_name
添加默认约束
ALTER TABLE users2 ALTER age SET DEFAULT 15;
删除默认约束
ALTER TABLE users2 ALTER age DROP DEFAULT;.
修改列定义
ALTER TABLE tb1_name MODIFY [COLUMN] col_name
column_definition [FIRST|AFTER col_name]
修改列名称
ALTER TABLE tb1_name CHANGE [COLUMN] old_col_name
new_col_name column_definition [FIRST|AFTER col_name]
数据表更名
ALTER TABLE tb1_name RENAME new_tb1_name
RENAME TABLE tb1_name TO new_tb1_name
单表更新
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={exp1DEFAULT}[,col_name2=...][WHERE where_condition];
例子：
不指定位置：UPDATE users set age = age + 5 - id,sex = 0; //使表里所有记录的age加5并且减去当前id值
指定位置： UPDATE users set age = age+ 10 WHERE id % 2=0; //取id为偶数的位置
单表删除
DELETE FROM tbl_name [WHERE where_conditon];
查询表达式select
SELECT select_expr [,select expr2...] 只查找某一个函数或表达式
[
FROM table_references 查询表名
[WHERE where_conditon] 查询条件
[GROUP BY {col_nameposition} [ASC|DESC],...] 按某个字段进行分组，相同的只显示第一个
[HAVING conditon] 分组时，给出显示条件
[ORDER BY {col_name|expr|position} [ASC|DESC],...] 排序
[LIMIT {[offset,]row_count|row_count OFFSET offset}] 限制返回数量
}
ps：select * from user group by username having age>18 order by id asc limit 3; 限制返回数量的limit一定要放在最后
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...//将查询结果写入数据表
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
//在表tdb_goods_cates中插入tdb_goods中的cate种类。
UPDATE table_references SET col_name1={expr1 DEFAULT} [,col_name2={expr2 DEFAULT}]... [WHERE where_condition]
Eg：update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name set goods_cate=cate_id;
//将 tdb_goods 表和 tdb_goods_cates 表通过 goods_cate=cate_name 连接，然后更新 goods_cate=cate_id
建表、查询、写入三合一：
CREATE TABLE tdb_goods_brands (
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
) SELECT brand_name FROM tdb_goods GROUP BY brand_name;
多表更新：
UPDATE tdb_goods INNER JOIN tdb_goods_brands ON tdb_goods.brand_name = tdb_goods_brands.brand_name
SET tdb_goods.brand_name = tdb_goods_brands.brand_id;
通过ALTER TABLE语句修改数据表结构
ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
连接
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
语法结构
table reference A
{[INNERCROSS] JOIN {LEFTRIGHT} [OUTER] JOIN}
table_reference B
ON condition_expr
2.数据表参照
table_reference
tbl_name [[AS] alias] table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name 或 tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。
1、内连接：在MySQL中JOIN,INNER JOIN,CROSS JOIN是等价的
2、外连接：LEFT JOIN左外连接；RIGHT JOIN右外连接
3、连接条件：使用ON设定连接条件，也可以用WHERE代替
· ON：通常使用它设定连接条件
· WHERE：进行结果集记录的过滤
4：内连接是返回左表及右表符合连接条件的记录
OUTER JOIN
1、LEFT JOIN：显示左表全部和左右符合连接条件的记录
2、RIGHT JOIN：显示左右符合连接条件的记录和右表全部记录
3、若某字段只存在某一表，则另一表的里字段返回null
多表删除
1、查找重复记录：
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;
2、删除重复记录：
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count（(goods_name) >= 2 ) AS t2
ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;
mysql -uroot -p
输入密码
修改mysql提示符
1.连接客户端时通过参数指定
mysql -uroot -p密码 --prompt 提示符
2.连接上客户端后，通过prompt
mysql>prompt 提示符
提示符：
\D 完整的日期
\d 当前数据库
\h 服务器名称
\u 当前用户
常用命令
SELECT VERSION();显示当前服务器版本
SELECT NOW();显示当前日期时间
SELECT USER();显示当前用户
SELECT DATABASE();显示当前数据库
查看当前服务器下的数据库
SHOW DATABASES;
创建数据库
CREATE ｛DATABASE | SCHEMA｝ [IF NOT EXITSTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
大括号必有 中括号可有可无
CREATE DATABASE t;
查看警告
SHOW WARNINGS;
修改数据库
ALTER ｛DATABASE | SCHEMA｝[db_name] [DEFAULT] CHARACTER SET [=] charset_name
删除数据库
DROP ｛DATABASE | SCHEMA｝ [IF EXITSTS] db_name
打开数据库
USE 数据库名称
创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
)
查看数据表列表
SHOW TABLES [FROM db_name]
查看数据表结构
SHOW COLUMNS FROM tb1_name
插入记录
INSERT tb1_name [(col_name,...)] {VALUES|value}(val....)
记录查找
SELECT exp，... FROM tb1_name
空值与非空
NULL NOT NULL
自动编号
AUTO_INCREMENT
主键约束
PRIMARY KEY
一张数据表只能有一个
主键保证数据的唯一性
每张数据表只能存在一个主键
主键自动为NOT NULL
唯一约束
UNIQUE KEY
每张数据表可以存在多个唯一约束
字段可以为空值
保证记录唯一性
自动编号
AUTO_INCREMENT
必须与主键同时使用
默认约束
DEFAULT
插入记录时，如果没有明确为字段赋值，则自动赋予默认值
添加单列
ALTER TABLE tb!_name ADD[COLUMN] col_name column_definition [FIRST|AFTER col_name];
添加多列
ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,...);
删除列
ALTER TABLE tb1_name DROP [COLUMN] col_name
添加默认约束
ALTER TABLE users2 ALTER age SET DEFAULT 15;
删除默认约束
ALTER TABLE users2 ALTER age DROP DEFAULT;.
修改列定义
ALTER TABLE tb1_name MODIFY [COLUMN] col_name
column_definition [FIRST|AFTER col_name]
修改列名称
ALTER TABLE tb1_name CHANGE [COLUMN] old_col_name
new_col_name column_definition [FIRST|AFTER col_name]
数据表更名
ALTER TABLE tb1_name RENAME new_tb1_name
RENAME TABLE tb1_name TO new_tb1_name
单表更新
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={exp1DEFAULT}[,col_name2=...][WHERE where_condition];
例子：
不指定位置：UPDATE users set age = age + 5 - id,sex = 0; //使表里所有记录的age加5并且减去当前id值
指定位置： UPDATE users set age = age+ 10 WHERE id % 2=0; //取id为偶数的位置
单表删除
DELETE FROM tbl_name [WHERE where_conditon];
查询表达式select
SELECT select_expr [,select expr2...] 只查找某一个函数或表达式
[
FROM table_references 查询表名
[WHERE where_conditon] 查询条件
[GROUP BY {col_nameposition} [ASC|DESC],...] 按某个字段进行分组，相同的只显示第一个
[HAVING conditon] 分组时，给出显示条件
[ORDER BY {col_name|expr|position} [ASC|DESC],...] 排序
[LIMIT {[offset,]row_count|row_count OFFSET offset}] 限制返回数量
}
ps：select * from user group by username having age>18 order by id asc limit 3; 限制返回数量的limit一定要放在最后
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...//将查询结果写入数据表
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
//在表tdb_goods_cates中插入tdb_goods中的cate种类。
UPDATE table_references SET col_name1={expr1 DEFAULT} [,col_name2={expr2 DEFAULT}]... [WHERE where_condition]
Eg：update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name set goods_cate=cate_id;
//将 tdb_goods 表和 tdb_goods_cates 表通过 goods_cate=cate_name 连接，然后更新 goods_cate=cate_id
建表、查询、写入三合一：
CREATE TABLE tdb_goods_brands (
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
) SELECT brand_name FROM tdb_goods GROUP BY brand_name;
多表更新：
UPDATE tdb_goods INNER JOIN tdb_goods_brands ON tdb_goods.brand_name = tdb_goods_brands.brand_name
SET tdb_goods.brand_name = tdb_goods_brands.brand_id;
通过ALTER TABLE语句修改数据表结构
ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
连接
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
语法结构
table reference A
{[INNERCROSS] JOIN {LEFTRIGHT} [OUTER] JOIN}
table_reference B
ON condition_expr
2.数据表参照
table_reference
tbl_name [[AS] alias] table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name 或 tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。
1、内连接：在MySQL中JOIN,INNER JOIN,CROSS JOIN是等价的
2、外连接：LEFT JOIN左外连接；RIGHT JOIN右外连接
3、连接条件：使用ON设定连接条件，也可以用WHERE代替
· ON：通常使用它设定连接条件
· WHERE：进行结果集记录的过滤
4：内连接是返回左表及右表符合连接条件的记录
OUTER JOIN
1、LEFT JOIN：显示左表全部和左右符合连接条件的记录
2、RIGHT JOIN：显示左右符合连接条件的记录和右表全部记录
3、若某字段只存在某一表，则另一表的里字段返回null
多表删除
1、查找重复记录：
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;
2、删除重复记录：
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count（(goods_name) >= 2 ) AS t2
ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;
