mysql常用的sql:
1.sql语言分类:
1.数据查询语言DQL:SELECT子句,FROM子句,WHERE
2.数据操纵语言DML:INSERT UPDATE DELETE
3.数据定义语言DDL:创建数据库中的各种对象-----表、视图、索引、同义词、聚簇
TABLE/VIEW/INDEX/SYN/CLUSTER DDL操作是隐性提交的!不能rollback
4. 数据控制语言DCL:数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,
对数据库实行监视等。如:
--1.GRANT:授权
--2.ROLLBACK [WORK] TO [SAVEPOINT]:回退到某一点
--1.COMMIT [WORK]:提交
2.提交数据的三种类型:
1.显式提交:SQL>COMMIT;
2.隐式提交:用SQL命令间接完成的提交为隐式提交:ALTER,AUDIT,COMMENT,CONNECT,CREATE,DISCONNECT,DROP,
EXIT,GRANT,NOAUDIT,QUIT,REVOKE,RENAME。
3.自动提交:若把AUTOCOMMIT设置为ON,则在插入、修改、删除语句执行后,
系统将自动进行提交,这就是自动提交。其格式为:
SQL>SET AUTOCOMMIT ON;
select @@autocommit; //查看一下autocommit的设置
select @@tx_isolation; //查看事物隔离级别
show variables like '%iso%'//查看事物隔离级别
1.表相关sql:
-- 查看数据库
show databases;
show databases like 'test';
-- 创建数据库
create DATABASE testdb;
show create DATABASE testdb;
-- 删除数据库
drop DATABASE tttest;
-- mysql存储引擎列表
show engines;
-- 查看mysql当前默认的存储引擎:
show variables like '%storage_engine%';
-- 查看某个库下指定表使用的存储引擎
show table status from ttest where name='tb_emp2';
-- 选择操作数据库
use testdb;
-- 查看数据表
show TABLES;
-- 数据表创建
create table temp1(
id int(11),
name VARCHAR(25),
deptId int (11),
salary FLOAT
);
-- 数据表创建 主键约束 -1
create table temp2(
id int(11) PRIMARY KEY,
name VARCHAR(25),
deptId int (11),
salary FLOAT
);
-- 数据表创建 主键约束 -2
create table temp3(
id int(11),
name VARCHAR(25),
deptId int (11),
salary FLOAT,
PRIMARY KEY(id)
);
-- 数据表创建 主键约束 -3 联合主键约束
create table temp4(
id int(11),
name VARCHAR(25),
deptId int (11),
salary FLOAT,
PRIMARY KEY(id,name)
);
-- 数据表创建 外键约束 CONSTRAINT <外键名称> FOREIGN KEY(deptId) REFERENCES temp5(id) id 和 deptId的数据类型必须一致
create table temp5(
id int(11),
name VARCHAR(25),
deptId int (11),
salary FLOAT,
PRIMARY KEY(id)
);
create table temp5_foreignkey(
id int(11),
name VARCHAR(25),
deptId int (11),
salary FLOAT,
PRIMARY KEY(id),
CONSTRAINT fk_forign_temp FOREIGN KEY(deptId) REFERENCES temp5(id)
);
-- 数据表创建 非空约束
create table temp6(
id int(11),
name VARCHAR(25) not null,
deptId int (11),
salary FLOAT,
PRIMARY KEY(id)
);
-- 数据表创建 唯一约束 可为null但是能有一个 -1
create table temp7(
id int(11),
name VARCHAR(25) UNIQUE,
deptId int (11),
salary FLOAT,
PRIMARY KEY(id)
);
-- 数据表创建 唯一约束 可为null但是能有一个 -2 CONSTRAINT <唯一约束名称> UNIQUE(name)
create table temp8(
id int(11),
name VARCHAR(25) ,
deptId int (11),
salary FLOAT,
PRIMARY KEY(id),
CONSTRAINT sth UNIQUE(name)
);
-- 数据表创建 默认约束 指定默认值
create table temp9(
id int(11),
name VARCHAR(25) DEFAULT '0000',
deptId int (11),
salary FLOAT,
PRIMARY KEY(id),
CONSTRAINT sth UNIQUE(name)
);
-- 数据表创建 主键自增 一个表中只能有一个自增约束,并且必须是主键的一部分
create table temp10(
id int(11) PRIMARY key AUTO_INCREMENT,
name VARCHAR(25) DEFAULT '0000',
deptId int (11),
salary FLOAT
);
-- 数据表修改 修改表名
show tables;
select t.table_name from information_schema.TABLES t where t.TABLE_SCHEMA ="testdb" and t.TABLE_NAME ="temp11";
ALTER TABLE temp10 RENAME temp11;
-- 数据表修改 修改字段数据类型
desc temp11;
ALTER TABLE temp11 MODIFY name VARCHAR(30);
-- 数据表修改 修改字段名称 及数据类型 -不同的数据类型的存储方式及长度有所不同,所以修改数据类型可能会导致数据丢失,所以在有数据时,慎改
desc temp11;
ALTER TABLE temp11 change `name` sname VARCHAR(30);
-- 数据表修改 添加字段 :开始最后指定字段
desc temp11;
ALTER TABLE temp11 add add_sname_last VARCHAR(30) ;
ALTER TABLE temp11 add add_sname_first VARCHAR(30) FIRST ;
ALTER TABLE temp11 add add_sname_my VARCHAR(30) AFTER id ;
ALTER TABLE temp11 add add_sname_my_not VARCHAR(30) not null AFTER id ;
ALTER TABLE temp11 add add_sname_my_not_default VARCHAR(30) not null DEFAULT 'haha' AFTER id ;
-- 数据表修改 删除字段
desc temp11;
ALTER TABLE temp11 drop add_sname_last ;
-- 数据表修改 修改位置
desc temp11;
ALTER TABLE temp11 MODIFY id int(11) FIRST;-- id修改为第一个字段
ALTER TABLE temp11 MODIFY id int(11) AFTER add_sname_first;-- id放在add_sname_first之后
-- 数据表修改 修改存储引擎
show table status from testdb where name='temp11';
ALTER TABLE temp11 engine=MyISAM;
ALTER TABLE temp11 engine=InnoDB;
-- 查看数据表结构
show create TABLE temp4;
DESCRIBE temp4;
desc temp4;
-- 组装json
SELECT
CONCAT('{',GROUP_CONCAT(CONCAT('"',COLUMN_NAME,'":0') SEPARATOR ","),'}'),
COUNT(0) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'ziniu' AND TABLE_NAME = 't_s_post_want_zn';
-- 组装sql insert
SELECT
CONCAT('insert into ',TABLE_NAME,'(',GROUP_CONCAT(COLUMN_NAME SEPARATOR ","),') values (',GROUP_CONCAT('?' SEPARATOR ","),')') as `sql`,
COUNT(0) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'temp4';
-- 组装sql update
SELECT
CONCAT('update ',TABLE_NAME,' set ',GROUP_CONCAT(CONCAT(COLUMN_NAME,'=?') SEPARATOR ",")) as `sql`,
COUNT(0) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'temp4';
-- 删除数据表
drop TABLE if EXISTS temp1;
-- 当结果提示警告时,可以使用以下语句查看警告
show warnings ;
-- 运算符
-- 算术
select 1/0; -- NULL
select 93/3; -- 整除31
select 1/3; -- 0.3333保留4位小数,四舍五入
-- 比较
-- 等于
select 1=0,1='1',null=null; -- 0 1 NULL
-- 安全等于
select 1<=>0,1<=>'1',null<=>null; -- 0 1 1
select 1<=>0,1<=>'1',null<=>1; -- 0 1 0
-- 不等于
select 1!=0,null!=null;
# Mysql添加和删除唯一索引、主键
# 添加 ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );
# 删除 ALTER TABLE `table_name` DROP PRIMARY KEY;
# 添加 ALTER TABLE `table_name` ADD UNIQUE ( `column` );
# 删除 ALTER TABLE `table_name` DROP INDEX `column`;