数据库SQL

一、MYSQL

1.DCL（Data Control Language):数据控制语句，用于定义用户的访问权限和安全级别。包括如下SQL语句:

GRANT：授予访问权限
REVOKE：撤销访问权限
COMMIT：提交事务处理
ROLLBACK：事务处理回退SAVEPOINT：设置保存点LOCK：对数据库的特定部分进行锁定

2 .DDL（Data Definition Language ) :数据定义语句，用于库和表的创建、修改、删除。包括如下SQL语句:

CREATE TABLE：创建数据库表
ALTER TABLE：更改表结构、添加、删除、修改列长度
DROP TABLE：删除表
CREATE INDEX：在表上建立索引
DROP INDEX：删除索引

3 .DML（Data Manipulation Language ) :数据操纵语句，用于添加、删除、修改、查询数据库记录。包括如下SQL语句:

INSERT：添加数据到数据库中
UPDATE：修改数据库中的数据
DELETE：删除数据库中的数据
SELECT：选择（查询）数据  

1.1 DCL（Data Control Language):数据库控制语句

1.2 DDL（Data Definition Language):数据定义语句

#展示所有数据库
show databases ; 
#创建数据库, create database 数据库名
CREATE DATABASE IF NOT EXISTS we_share default charset utf8 COLLATE utf8_general_ci ; 
#使用数据库, use数据库名称; 
use we_share;  
#删除数据库,DROP DATABASE 库名;
drop database we_share;
#查看数据库中所有表 
show tables;
#查看表的结构：desc 表名
desc `order`;

#建表
create table if not exists template (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL COMMENT '名称',
  `del_flag` int(11) NOT NULL DEFAULT '0' COMMENT '删除标记(0-未删除 1-已删除)',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='模板表';

#使用子查询创建表 CREATE TABLE 表名 AS  子查询结果
CREATE TABLE dept AS 
	SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80;

#变更表
ALTER TABLE `app_area`
ADD COLUMN `company_id` INT(11) NOT NULL COMMENT '公司id(app_company中的id)',
MODIFY COLUMN `type`  tinyint(4) NOT NULL COMMENT '用户类型（1-内部账号 2-客户账号）' AFTER `gender`,
CHANGE COLUMN `dept_id` `hold_dept_id`  int(11) NOT NULL COMMENT '持有方机构ID' ,
DROP COLUMN `company_id`;

ALTER TABLE `sys_dic` RENAME TO `sys_dict`
#删除表: drop table 表名
drop table `order`;
drop table if exists `order`;

#索引
CREATE INDEX index_name ON table_name (column_name)# 创建普通索引
CREATE UNIQUE INDEX index_name ON table_name (column_name) #创建唯一索引  两行索引值绝对不能相同
#添加索引
ALTER TABLE `app_mach_day`
ADD UNIQUE INDEX `uk_machid_date` (`mach_id`, `date`),
ADD INDEX `idx_time_machId_deviceId` (`time`, `mach_id`, `device_id`) USING BTREE ,
DROP INDEX `FK_app_project_sys_dept`,  #删除索引
ADD CONSTRAINT `FK_app_project_sys_dept` FOREIGN KEY (`dept_id`) REFERENCES `sys_dept` (`id`), #新增外键
DROP FOREIGN KEY `FK_app_project_sys_dept`; #删除外键

数据库操作

#从系统表中查询数据库
SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME='databaseName';
SELECT DISTINCT * FROM information_schema.TABLES t, information_schema.SCHEMATA n WHERE t.table_name = 'tableName' AND n.SCHEMA_NAME = 'databaseName';

1.3 DML（Data Manipulation Language):数据操纵语句

1 增

# insert into 表名(列名列表) values(列对应的值的列表);
#如果表全列匹配则可以删除列名表名
insert into t_user(id,username,password,realname)  values ( 1,'jack1','000000','jack4');
#全列匹配等同于
insert into t_user values (2,'jack2','000000','jack4');
#批量插入
insert into t_user(id,username,password,realname) values
( 3,'jack4','000000','jack4'),( 4,'jack5','000000','jack5'),
( 5,'jack6','000000','jack6'),( 6,'jack7','000000','jack7');
#插入子查询 INSERT INTO 表名（列名） 子查询结果 （并不需要 values）
INSERT INTO emp2 SELECT * FROM employees WHERE department_id = 90;

1表中插入 null
(1)隐式方式: 在列名表中省略该列的值
INSERT INTO t_user(id,username ) VALUES (7, 'Purchasing');
(2)显示方式: 在VALUES 子句中指定空值
insert into t_user(id,username,password,realname) values (8,'jack8',null,null);
#全列匹配等同于
insert into t_user values (8,'jack8',null,null);

#插入指定值,如系统时间 now()
insert into t_stu(id,username,password,update_time ) values (8,'jack8','000000', now());

#存在则更新，不存在则插入
INSERT INTO t_stu(id,username,password,update_time ) VALUE(8,'jack8','11111', now()) ON DUPLICATE KEY UPDATE name= '小李',password='123456'

#使用文件导入
load data infile 'e:\\d.txt' into table t_stu fields terminated by',' lines terminated by'\r\n'

2改

# update 表名 set 列1 = 列1的值, 列2 = 列2的值 where
UPDATE t_stu SET username = 'jack7',password = 70 WHERE id = 7;

3删

# delete from 表名 where ….
DELETE FROM t_stu WHERE username = 'jack7';
#省略 where 条件,执行全表删除
DELETE FROM copy_emp;
#清空表
TRUNCATE TABLE detail_dept;

4查

# select 列名1,列名2, … from 表名

1.4 函数

用法 select function(列) from 表

1.字符函数
#1.1 大小写控制函数
LOWER('SQL Course')    # sql course
UPPER('SQL Course')    # SQL COURSE
#1.2 字符控制函数
CONCAT('Hello', 'World')    #连接     HelloWorld
SUBSTR('HelloWorld',1,5)    #截取子串  Hello
LENGTH('HelloWorld')        #长度     10
INSTR('HelloWorld', 'W')    #位置     6
LPAD(salary,10,'*')         #左侧添加  *****24000
RPAD(salary, 10, '*')       #右侧添加  24000*****
TRIM('H' FROM 'HelloWorld') #截取     elloWorld
REPLACE('abcd','b','m')     #替换     amcd
2.数字函数
ROUND(45.926, 2)           #四舍五入  45.93
TRUNC(45.926, 2)           #截断     45.92
MOD(1600, 300)             #求余     100
floor(11.84)               #向下取整  11
3.时间函数
#系统当前时间
select now();
STR_TO_DATE('9-13-1999','%m-%d-%Y')    #指定格式    1999-09-13
DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’) #指定格式     2018年06月06日


#返回当前数据库
select database();
#返回当前登录用户
select user();   #root@192.168.2.48

2表结构语句

#查询表中自选信息
SELECT * FROM INFORMATION_SCHEMA.Columns WHERE table_name = 'user' and column_name = 'username'  and table_schema = database()
#判断字段是否存在
SELECT EXISTS (); 判断存在函数，存在返回1，不存在返回0；