MySql语句

1. 查看

show database;  -- 查看所有数据库
show tables;    -- 查看所有表
show engines;   -- 查看存储引擎

-- 查看表的结构(以下五条语句效果相同）
DESC n;   # 因为简单，所以建议使用
DESCRIBE n;
SHOW COLUMNS IN n;
SHOW COLUMNS FROM n;
EXPLAIN n;
-- 查看表的创建语句
SHOW CREATE TABLE n;

2. 创建

create database 库名;   -- 创建数据库
create table 表名;      -- 创建表
create table 表名(列名1 数据类型 约束条件 ,列名2 数据类型 约束条件 .....);

-- 直接将查询结果导入或复制到新创建的表
CREATE TABLE 表名 SELECT * FROM 查的表;
-- 新创建的表与一个存在的表的数据结构类似
CREATE TABLE 表名 LIKE 参考的表;

例：create table employee (id int(10), name char(20), phone int(12));

3. sql中的约束

（1）主键primary key ：是用于约束表中的一行，作为这一行的唯一标识符，在一张表中通过主键就能准确定位到一行。主键不能有重复且不能为空。

如果是创建的时候就定义，直接primary key(列名) ，例：primary key (id)
如果是后来想为表添加主键的话：alter table employee add primary key (id)

（2）默认值default ：当有 DEFAULT 约束的列，插入数据为空时，将使用默认值。

（3）唯一约束unique ：规定一张表中指定的一列的值必须不能有重复值。当 INSERT 语句新插入的数据和已有数据重复的时候，如果有 UNIQUE约束，则 INSERT 失败。

（4）外键约束foreign key：需要建立两表间的关系，外键必须参考references另一个表的主键，对应的主键中必须有对应值。

语法：[constraint 外键约束名] foreign key(当前表的字段名) references 参照表 (参照表的字段名)　　 # 如果没写“constraint 外键约束名”的话，会自动生成一个外键约束名。
- 例：foreign key (emp_id) references employees (id);的意思就是说当前表的emp_id字段是以employees的id字段为外键的。
添加外键：alter table 表名 add constraint 外键约束名 foreign key(列名) references 引用外键表(列名)

【注意】：

1）关系中的所有表必须是InnoDB表，在非InnoDB表中，MySQL将会忽略FOREIGN KEY…REFERENCES修饰符。（InnoDB支持外键，MyISAM不支持外键）

2）用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。

4）外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以。

3）即使表存在外键约束，MySQL还允许我们删除表，并且不会产生错误。删除外键方法：ALTER TABLE table-name DROP FOREIGN KEY key-id。

（5）非空约束not null ：被非空约束的列，在插入值时必须非空。

例创建goods表：

CREATE TABLE `goods` (
  `goods_id` int(11) NOT NULL auto_increment,
  `goods_name` varchar(20) NOT NULL,
  `goods_price` float default NULL,
  `goods_num` int(11) default NULL,
  `goods_date` date default NULL,
  `goods_type` int(11) default NULL,
  `goods_img` varchar(200) default NULL,
  PRIMARY KEY  (`goods_id`),
  KEY `goods_type` (`goods_type`),
  CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`goods_type`) REFERENCES `goods_type` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. 增加

（1）在表中增加一列：

alter table 表名 add column 列名 数据类型 约束;
alter table 表名 add 列名 数据类型 约束;
alter table 表名 add 列名 数据类型 约束 after 前面的列名;
alter table 表名 add 列名 数据类型 约束 first;
例：
alter table employee add height int(4) default 170;
alter table employee add height int(4) default 170 after age;

（2）在表中插入一行：

insert into 表名(列名,列名,...) values(值,值,...)
例：
insert into employee (id, name, phone) values (01,’Tom’,110110110);
insert into employee values (02,’Jack’,119119119);
insert into employee (id, name) values (03,’Rose’);

5. 删除

-- 删除数据库：
drop database 数据库名;

-- 删除表：
drop table 表名；

-- 删除一列：
（1）alter table 表名 drop column 列名;
（2）alter table 表名 drop 列名;

-- 删除一行：
delete from 表名 where 条件;
例：delete from employee where name=’Tom’;

-- 清空一个表中的数据：
delete from 表名;

6. 查看

-- 查看表的结构（查看数据表中列的名称和类型）：
describe 表名;

-- 查看数据库和表：
    查看数据库：show databases;
 使用这个数据库：use 数据库名
        查看表：show tables;
显示表中全部数据：select * from 表名;

-- 查看表Select：
select * from course;
（条件）
select name,age from employee where age>30;    
（联合条件）
select name,age from employee where age>20 and age<30;
select name,age from employee where age between 20 and 60;
（范围）
select name,age,in_dpt from employee where in_dpt in (‘dpt3’,’dpt4’);

-- 子查询：查询一个数值在另外一个表中的映射
select of_dpt ,count(proj_num) from project where of_dpt in (select in_dpt from employee where name=’Tom’);

-- 连接查询：
（1）select id,name,count from employee,department where employee.in_dpt =department.dpt_name order by id;
（2）select id,name,count from employee join department on employee.in_dpt=department.dpt order by id;

-- 使用limit子句显示数据表第一行数据：
select article, price from shop order by price desc limit 1;

7. 键

主键：索引列的值必须唯一，且不允许有空值

唯一键：索引列的值必须唯一，但允许有空值

# 添加主键
ALTER TABLE n ADD PRIMARY KEY (id);
ALTER TABLE n ADD CONSTRAINT pk_n PRIMARY KEY (id);   # 主键只有一个，所以定义键名似乎也没有什么用
# 删除主键
ALTER TABLE n DROP PRIMARY KEY ;
# 添加外键
ALTER TABLE m ADD FOREIGN KEY (id) REFERENCES n(id);    # 自动生成键名m_ibfk_1
ALTER TABLE m ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES n(id);   # 使用定义的键名fk_id
# 删除外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`;
# 修改外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`, ADD CONSTRAINT fk_id2 FOREIGN KEY (id) REFERENCES n(id);    # 删除之后从新建
# 添加唯一键
ALTER TABLE n ADD UNIQUE (name);
ALTER TABLE n ADD UNIQUE u_name (name);
ALTER TABLE n ADD UNIQUE INDEX u_name (name);
ALTER TABLE n ADD CONSTRAINT u_name UNIQUE (name);
CREATE UNIQUE INDEX u_name ON n(name);
# 添加索引
ALTER TABLE n ADD INDEX (age);
ALTER TABLE n ADD INDEX i_age (age);
CREATE INDEX i_age ON n(age);
# 删除索引或唯一键
DROP INDEX u_name ON n;
DROP INDEX i_age ON n;

8. 视图

-- 创建视图
CREATE VIEW 视图名 AS SELECT id, name FROM 表名;
CREATE VIEW 视图名(id, name) AS SELECT id, name FROM 表名;

-- 查看视图(与表操作类似)
SELECT * FROM 视图名;
DESC 视图名;

-- 查看创建视图语句
SHOW CREATE VIEW 视图名;

-- 更改视图
CREATE OR REPLACE VIEW 视图名 AS SELECT name, age FROM 表名;
ALTER VIEW 视图名 AS SELECT name FROM 表名 ;

-- 删除视图
DROP VIEW IF EXISTS 视图名;

9. 连接查询

（1）内连接：内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。

（2）外连接：分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN) 和全外连接(FULL OUTER JOIN或FULL JOIN)三种。

　　与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

（3）交叉连接：(CROSS JOIN)没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。

-- 内联接
SELECT * FROM m INNER JOIN n ON m.id = n.id;

-- 左外联接
SELECT * FROM m LEFT JOIN n ON m.id = n.id;
-- 右外联接
SELECT * FROM m RIGHT JOIN n ON m.id = n.id;

-- 交叉联接
SELECT * FROM m CROSS JOIN n;   # 标准写法
SELECT * FROM m, n;

-- 类似全连接full join的联接用法
SELECT id,name FROM m
UNION
SELECT id,name FROM n;

10. 存储过程

-- 创建存储过程
# 无参数
CREATE PROCEDURE getDates()
  BEGIN
    SELECT * FROM test ;
  END 
# in参数
CREATE PROCEDURE getDates_2(IN id INT)    
  BEGIN
    SELECT * FROM test WHERE a = id;
  END 
# out参数
CREATE PROCEDURE getDates_3(OUT sum INT)  
  BEGIN
    SET sum = (SELECT count(*) FROM test);
  END 
# inout参数
CREATE PROCEDURE getDates_4(INOUT i INT) 
  BEGIN
    SET i = i + 1;
  END 

-- 删除存储过程
DROP PROCEDURE IF EXISTS getDates;

-- 修改存储过程的特性
ALTER PROCEDURE getDates MODIFIES SQL DATA ;

-- 查看存储过程
SHOW PROCEDURE STATUS LIKE 'getDates';    # 状态
SHOW CREATE PROCEDURE getDates_3;   # 语句

-- 调用存储过程
CALL getDates();
CALL getDates_2(1);

参考MYSQL基础常见常用语句200条

　　https://jingyan.baidu.com/article/60ccbceb9578f164cab197f4.html