数据库基本操作
启动数据库
mysql -u用户名 -p密码
DDL语句
- DDL(data definition language)数据定义语言
- 就是我们在创建表时用到的一些SQL语句。例如：CREATE、ALTER、DROP等。DDL主要是用在定义表或者改变表的结构、数据类型、表之间的链接和约束等初始化操作上。
数据库操作语句
- 查询当前所有数据库名称：
show databases; - 创建数据库：
create database 数据库名; - 如果不存在创建一个新的数据库：
create database if not exists 数据库名; - 删除数据库：
drop database 数据库名; - 判断删除（如果存在就删除）：
drop database if exists 数据库名;
数据库表操作语句
- 创建表之前要先声明给哪个数据库创建：
use 数据库名;
-
创建表：
create table 表明(列名1 字段类型，列名2 字段类型…); create table 表明(列名1 字段类型，列名2 字段类型…)ENGINE = INNODB charset = utf8; //eg create table 表名 ( id int primary key, //主键约束 name char(20) unique, //唯一性约束 sex char(4) default '男' not null, //默认约束,如果字段为空将默认为男 age int check(age>=18 and age<=45), //检查约束，指定字段中输入数值的范围,年龄是大于等于18和小于等于45 game_level int not null //非空约束,指定字段不能为空 )上面ENGINE=InnoDB使用innodb引擎，从zhidaoMySQL 5.6开始默认使用该引擎
CHARSET=utf8 数据库默认编码为utf-8
字段类型
-
整数类型：只能存储整数
-
标准整型：int，使用4个字节存储整数
-
数值型存储在MySQL中分为有符号（有负数）和无符号（纯正数）需要unsigned 修饰整型
-
小数类型：可以存储有效数值
-
浮点数又称之为精度数据，分为两种
- 单精度：float，使用4个字节存储，精度范围为6-7位有效数字
- 双精度：double，使用8个字节存储，精度范围为14-15位有效数字
-
浮点数超过精度范围会自动进行四舍五入
-
字符串类型：存储字符串数据
1、定长型：char(L)，指定固定长度的存储空间存储字符串
- 定长是指定存储长度
- 定长的长度是字符而不是字节
- 字符串数据使用单引号或者双引号包裹
2、变长型：varchar(L)，根据实际存储的数据变化存储空间
- 变长型的存储空间是由实际存储数据决定的
- 变长型的L也是指字符而不是字节
- 时间日期类型：存储时间日期格式数据
1、年：year，MySQL中用来存储年份的类型
2、时间戳：timestamp，基于格林威治时间的时间记录
- MySQL中时间戳表现形式不是秒数，而是年月日时分秒：格式
- YYYY-MM-DD HH:II::SS
- YYYYMMDDHHIISS
3、日期：date，用来记录年月日信息
- 使用3个字节存储数据
- 存储日期的格式为：YYYY-MM-DD
表约束
NOT NULL 指定字段不能包含空值
UNIQUE 指定字段的值（或字段组合的值）表中所有的行必须唯一，创建唯一性索引，加快查询速度。
PRIMARY KEY 表的每行的唯一标识，即主键。建立主键索引。记录不能为空。
FOREIGN KEY 在字段和引用表的一个字段之间建立并且强制外键关系，即外键
CHECK 指定一个必须为真的条件- 主键与唯一约束的区别:
1.主键只能有一个，而唯一约束可以有多个；
2.主键可以由一列或多列充当，但唯一约束只能一列一列创建；
3.主键不允许为空，而唯一约束在Oracle中可以多次为空，在SQL中唯一约束只能一次为空；NOT NULL:此约束为行级约束，不在能表级约束中定义。
-
-
查看数据库中所有表：
show tables; -
查看当前表的结果：
desc 表名; -
修改中文乱码：
alter database 数据库名 default character set 字符集; -
添加一个新的列名：
alter table 表名 add 列名 字段类型; -
修改表字段名称：
alter table 表名 change 旧列名 新列名 字段类型; -
修改表字段类型：
alter table 表名 modify 列名 字段类型; -
修改表名：
alter table 当前表名 rename to 新的表名; -
删除表中某个列：
alter table 表名 drop 列名; -
删除表：
drop table 表名；/drop table if exists 表名; -
复制表：
create table 新表名 like 其他表名;-
create table 新表名 like 其他表名;
create table 新表 as select * from 旧表 -
复制表结构不需要数据：
create table 新表 as select * from 旧表 where 2<>2
-
DML语句
- DML(data manipulation language) 数据操纵语言
- 就是我们经常用到的SELECT、UPDATE、INSERT、DELETE。主要用来对数据库的数据进行的一些操作。
INSERT插入记录
-
方式一：默认全部插入数据
insert into 表名 values(值1, 值2, 值3…); insert into student values(1,"小明",20181601); //例子 -
方式二：部分插入
insert into 表名(列名1, 列名2, 列名3) values(值1, 值2, 值3);
UPDATE修改记录
- 修改一条
update 表名 set 字段名称=值 where 条件语句;
- 批量修改：
update 表名 set 字段名称;
- eg：
update student set name=’张三‘ where id=1;(把id是1的学生名改为张三)
UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3);
DELETE删除记录
-
删除表：
delete from 表名; -
带条件删除：
delete from 表名 where 条件; eg: 列名称='某值' -
delete和truncate的区别？
- delete from 表名：删除全表中记录，针对自增长主键的字段不受影响，1,2，3下一次从4开始增长；
- truncate from 表名：删除一张表，在复制一张一模一样的表，针对自增长主键字段置空，下一次插入数据从1开始一直自增。
SELECT查询记录
SELECT 列名称 FROM 表名称;
LOAD加载数据
-
LOAD DATA INFILE 语句以非常高的速度从文本文件中读取行到表中。
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (partition_name,...)] [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var,...)] [SET col_name = expr,...] -
eg:
将数据存放在目录为
/home/erik/Documents/out10-2.csv,根据数据特点，只需执行如下操作mysql> LOAD DATA LOCAL INFILE '/home/erik/Documents/out10-2.csv' -> INTO TABLE out10 -> FIELDS TERMINATED BY ',' -> IGNORE 1 LINES -> ;LOAD DATA **LOCAL** INFILE ‘/home/erik/Documents/out10-2.csv，这里要加上“LOCAL”，然后后面跟本地文件路径，否则会报错。FIELDS TERMINATED BY ','是将要导入的数据以逗号为分割标志传入到 MySQL 数据库中，因为之前在 Excel 保存的 CSV 文件默认以逗号为分割。IGNORE 1 LINES这里忽略第一行，因为 CSV 文件第一行是标签，创建表时已经定义了相应的字段，这里不用输入到数据库中。
DQL语句
- DQL(Data Query Language)数据查询语言.
- 是用来进行数据库中数据的查询的，即最常用的select语句.
单表查询
-
对表的查询语句
-
基本查询：
select * from 表名; -
指定查询：
select 列名1,列名2 from 表名; -
查询字段的时候去重：
select distinct 字段 from 表名;
-
-
给字段起名字（as）也可以省略：
select name （as）‘姓名’，sex ‘性别’ from 表名; -
条件查询：查询年龄大于20的姓名和年龄
select name,age from student where age>20; -
范围查询：查询年龄在20到30的学生
select * from student where age between 20 and 30; -
查询多个数据用in（类似于集合数据）
select * from student where age in(18,28,30); -
模糊查询：关键字like
select * from 表名 where 字段名称 like ‘占位符号’;%包含的字符，相当于一个字符
_ 代表单个字符
eg：’%张%’ 名字里有张的
‘马_’姓马，名字里有两个字符 -
排序：
select * from 表名 order by 字段名称 默认升序排序；ASC 升序 DESC 降序
-
分组查询：group by
select sex，count(id)from student group by sex; -
分页查询：limit
select * from 表名 limit 起始行数,每页显示条数;- 起始行数=（当前页码数-1）也就是索引值 * 每页显示条数
-
筛选：having
- 可跟聚合函数
select 分组字段,聚合函数 from 表名 where 条件 //1 group by 分组字段 //2 having 聚合函数 符合什么条件; //3
-
having和group by的区别
- GROUP BY用于对查询结果按照某个字段或表达式进行分组，HAVING用于对分组结果进行过滤。
- HAVING必须和GROUP BY一起使用，或者只使用GROUP BY不使用HAVING。
- WHERE子句先执行，再执行GROUP BY分组，最后执行HAVING过滤。
- GROUP BY必须在WHERE子句之后，ORDER BY子句之前，HAVING必须在ORDER BY子句之后。
-
查询总结
select 字段列表 from 数据源 [ where条件表达式 ] [ group by 分组字段] [ having条件表达式 ] [ order by 排序字段 [ asc | desc ] ];执行顺序：
- FROM 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
- ON 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
- JOIN 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
- WHERE 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
- GROUP BY 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
- WITH CUBE or WITH ROLLUP 对表VT5进行cube或者rollup操作，产生表VT6.
- HAVING 对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。
- SELECT 执行select操作，选择指定的列，插入到虚拟表VT8中。
- DISTINCT 对VT8中的记录进行去重。产生虚拟表VT9.
- ORDER BY 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
- TOP 取出指定行的记录，产生虚拟表VT11, 并将结果返回。
这些步骤执行时，每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入.这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只是最后一步生成的表才会返回 给调用者。
多表查询
-
等值连接
-
等值连接就是两张或多张表的部分字段值相等并联合查询。等值连接也叫做简单连接。
-
等值连接查询的语法规则：(表：table1，table2。字段：id，name)
SELECT table1.id,table1.name,table2.name FROM table1,table2 WHERE table1.id=table2.id -
使用表别名进行多表等值连接查询
相对于上面的写法，如果表名很长并且不止一张表，那么怎末办呢，在这里我们可以给表起一个别名。
表（字段）：
t_student（stu_id,teacher_number,name,age）学生表（学号,老师编号,年龄）
t_teacher（number,subject_num）老师表（教师编号，科目号）
t_subject（sub_id,sub_name）科目表（科目编号，科目名称)SELECT s.name,s.age,j.sub_name FROM t_student s,t_teacher t,t_subject j WHERE s.teacher_number=t.number AND t.subject_num=j.sub_id AND s.stu_id=1
-
-
-
自然连接、内连接、外连接（左外连接、右外连接、全外连接）、交叉连接
两张表
student(id, name, code)
id name code 1 张三 20181601 2 李四 20181602 3 小红 20181603 4 小明 20181604 5 小青 20181605 score(id, grade, code)
id grade code 1 55 20181601 2 88 20181602 3 99 20181605 4 33 20181611 建表插入数据：
create table student(id int,name char(5),code int) ENGINE = INNODB charset = utf8; create table score(id int,grade int,code int) ENGINE = INNODB charset = utf8; insert into student values(1,"小明",20181601); insert into student values(2,"李四",20181602); insert into student values(3,"小红",20181603); insert into student values(4,"小明",20181604); insert into student values(5,"小青",20181605); insert into score values(1,55,20181601); insert into score values(2,88,20181602); insert into score values(3,99,20181605); insert into score values(4,33,20181611);-
自然连接（natural join）
- 自然连接不用指定连接列，也不能使用ON语句，它默认比较两张表里相同的列，
SELECT * FROM student NATURAL JOIN score;查询结果如下：
id code name grade 1 20181601 张三 55 2 20181602 李四 88 -
内连接（inner join）
- 和自然连接区别之处在于内连接可以自定义两张表的不同列字段。
- 内连接有两种形式：显式和隐式。
- 隐式的内连接，没有INNER JOIN，形成的中间表为两个表的笛卡尔积。
SELECT student.name,score.code FROM student,score WHERE score.code = student.code;- 显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。
SELECT student.name,score.code FROM student INNER JOIN score ON score.code = student.code;查询结果如下：语句执行结果相同。
name code 张三 20181601 李四 20181602 小青 20181605 -
外连接（outer join）
-
左外连接（left outer join）返回指定左表的全部行+右表对应的行，如果左表中数据在右表中没有与其相匹配的行，则在查询结果集中显示为空值。
SELECT student.name,score.code FROM student LEFT JOIN score ON score.code = student.code;查询结果如下：
name code 张三 20181601 李四 20181602 小青 20181605 小红 (NULL) 小明 (NULL) -
右外连接（right outer join）：与左外连接类似，是左外连接的反向连接。
SELECT student.name,score.code FROM student RIGHT JOIN score ON score.code = student.code;查询结果如下：
name code 张三 20181601 李四 20181602 小青 20181605 (NULL) 20181611 -
全外连接（full outer join）：把左右两表进行自然连接，左表在右表没有的显示NULL，右表在左表没有的显示NULL。（MYSQL不支持全外连接，适用于Oracle和DB2。）
- 在MySQL中，可通过求左外连接与右外连接的合集来实现全外连接。
SELECT student.name,score.code FROM student LEFT JOIN score ON score.code = student.code UNION SELECT student.name,score.code FROM student RIGHT JOIN score ON score.code = student.code;查询结果如下：
name code 张三 20181601 李四 20181602 小青 20181605 小红 (NULL) 小明 (NULL) (NULL) 20181611
-
-
交叉连接（cross join）：
- 相当与笛卡尔积，左表和右表组合。
SELECT student.name,score.code FROM student CROSS JOIN score ON score.code=student.code;查询结果如下：
name code 张三 20181601 李四 20181602 小青 20181605
-
逻辑运算与函数
-
逻辑运算
操作符包括如下几种：
- 算术操作符：算术操作符包括加(+)、减(-)、乘(*)、除(/)
- 比较操作符：比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等
- 逻辑操作符：逻辑操作符包括与(AND)、或(OR)和非(NOT)。
- 集合操作符：集合操作符包括冻并集(UNION)、交集(INTERSECT)、剪集(MINUS)
- 连接操作符：|| 例：SELECT ename || ‘ is a ‘ || job FROM emp;
集合操作符：多用于数据量比较大的数据局库，运行速度快。
-
union该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
SELECT ename, sal, job FROM emp WHERE sal >3000 UNION SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; -
union all该操作符与union 相似，但是它不会取消重复行，而且不会排序。
SELECT ename, sal, job FROM emp WHERE sal >2500 UNION ALL SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; -
intersect使用该操作符用于取得两个结果集的交集。
SELECT ename, sal, job FROM emp WHERE sal >2500 INTERSECT SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; -
minus使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。（MINUS 就是减法的意思）
SELECT ename, sal, job FROM emp WHERE sal >2500 MINUS SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
-
- 集合运算中各个集合必须有相同的列数，且类型一致，集合运算的结果将采用第一个集合的表头作为最终的表头，order by必须放在每个集合后。
-
函数
-
常用的系统函数
-
日期函数：
-
ADD_MONTHS(d,f)
指定时间d，推移f月，得到推移后的时间
d:指定一个时间(需要使用to_date函数转换)
f：在指定时间上推移多少个月
例：在“2010-05-12”时间上推移3个月后的时间
select ADD_MONTHS(to_date('2012-05-12','yyyy-mm-dd'),3) from dual; -
Months_between(d1,d2)
显示两个时间相差的月份
d1:第一个时间
d2：第二个时间注意：必须注意的是，d1与d2都为Date类型，不然会出现错误。 须用to_date(”,”) 来转换为日期格式，才能参加计算。
例：计算 2012-12-12 与 2012-2-12 相差的月份。
SELECT MONTHS_BETWEEN(to_date('2012-12-12','yyyy-MM-dd'),to_date('2012-2-12','yyyy-MM-dd')) FROM dual; -
last_day(m)
返回特定日期所在月份的最后一天
m:时间
例：计算“2010-10-12”所在月份的最后一天
SELECT last_day(to_date('2010-10-12','yyyy-mm-dd')) FROM dual; -
next_day(x,y)
用于计算x时间后第一个星期y的时间
例子，当前时间是2014-08-15
select next_day(to_date('2014-08-15','yyyy-mm-dd'),'星期二'）from dual;返回的结果是: 2014-08-19
-
trunc(date,[fmt])
处理时间
date:一个日期值
fmt :日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
例：
trunc(sysdate,'yyyy') --返回当年第一天. trunc(sysdate,'mm') --返回当月第一天. trunc(sysdate,'d') --返回当前星期的第一天. trunc(sysdate,'dd')--返回当前年月日 trunc(sysdate, 'hh')--返回当前小时 trunc(sysdate, 'mi')--返回当前分钟 -
trunc(number,[decimals])
处理数字
number: 待做截取处理的数值
decimals:指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分
例：
TRUNC（89.985，2）=89.98 TRUNC（89.985）=89 TRUNC（89.985，-1）=80
-
-
-
常用的字符函数
-
lower(string)
转小写
例：将字符“ABC”转为小写
select lower('ABC') from dual;结果：abc
-
upper()
转大写
例：将字符“abc”转为大写
select upper('abc') from dual;结果：ABC
-
length()
长度函数
注：长度是指字符串的长度 如“中国”为2 “ab”也为2
例：将字符“abc”转为大写
select length('abc') from dual;结果：3
-
substr(char, m, n)
截取字符串
例：将字符“abcde”中的“cd”进行截取
select substr('abcde', 3 ,2 ) from dual;结果：cd
-
replace(s1,s2)
替换
例：将字符“abcde”中的“c”替换为“123”;
select replace('abcde', 'c' ,'123' ) from dual;结果：ab123de
-
lpad( string, padded_length, [ pad_string ] )
指定长度,不够则填充
例：将字符“abcde”以10个长度显示，左侧用“X”填充;
select lpad('abcde',10,'x') from dual;结果：xxxxxabcde
-
-
数字函数
ceil：往上取整,与小数位的大小无关
floor:往下取整，与小数位的大小无关.
mod:取余。求模。
round:四舍五入。
trunc(m,n):
-
转换函数
-
to_char
日期转化为字符串
例：
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年 select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月 select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日 select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时 select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分 select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒 select to_char(sysdate,'day') as nowSecond from dual; //获取时间的星期 -
to_date
把字符串类型日期转换为date类型日期
例：
select to_date('2014-02-11','yyyy-mm-dd') from dual -
sysdata
当前日期
-
to_number
把某种类型转换为数字类型
注意：如果字符类型的内容是数据，则可以实现自动转换为数字类型
-
-
其它函数
-
NUL函数
把数字类型为null的值转换为0
结构：
nul(字段名称,0);
nul(comm,0);转换之后，可以实现算术运算。
-
decode函数
decode(参数一,参数二,参数三,….)
参数一：字段名称
参数二：参数一字段对应的内容。
参数三：把参数一字段对应的内容替换成其它的内容。例：
select decode(JOB,'CLERK','业务员') from emp;
-
-
聚合函数
AVG :返回指定组中的平均值。
COUNT:返回指定组中项目的数量。
MAX:返回指定数据的最大值。
MIN:返回指定数据的最小值。
SUM:返回指定数据的和，只能用于数字列。
-
DCL语句
- DCL(Data Control Language)数据控制语言
- 用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，能够对数据库进行监视。
- 比如常见的授权、取消授权、回滚、提交等等操作。
-
创建用户
CREATE USER 用户名@地址 IDENTIFIED BY '密码';-
创建一个testuser用户，密码111111
create user testuser@localhost identified by '111111';
-
-
给用户授权
GRANT 权限1, … , 权限n ON 数据库.对象 TO 用户名;-
将test数据库中所有对象(表、视图、存储过程，触发器等。*表示所有对象)的create,alter,drop,insert,update,delete,select赋给testuser用户
grant create,alter,drop,insert,update,delete,select on test.* to testuser@localhost;
-
-
撤销授权
REVOKE权限1, … , 权限n ON 数据库.对象 FORM 用户名;-
将test数据库中所有对象的create,alter,drop权限撤销
revoke create,alter,drop on test.* to testuser@localhost;
-
-
查看用户权限
SHOW GRANTS FOR 用户名;-
查看testuser的用户权限
show grants for testuser@localhost;
-
-
删除用户
DROP USER 用户名;-
删除testuser用户
drop user testuser@localhost;
-
-
修改用户密码
USE mysql; UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’; FLUSH PRIVILEGES;-
将testuser的密码改为123456
update user set password=password('123456') where user='testuser' and host=’localhost’; FLUSH PRIVILEGES;
-
markdowwn写的文档直接粘过来有些问题(´இ皿இ｀)