mysql的基础和语法代码

sql语言包括以下四个部分:

DML(数据操作语言Data Manipulation Language)	插入,修改,删除表中数据,入insert,update,delete
DDL(数据定义语言Data Definition Language)	创建或删除数据库对象,create,drop,alter
DQL(数据查询语言Data Query Language)	用来对数据库的数据进行查询select
DCL(数据控制语言Data Control Language)	用来控制数据库组件的存取权限等grant,revoke

 mysql常用属性约束:

字段属性,约束名	关键字	说明
非空约束	NOT NULL	如果该字段不允许为空,需要设置NOT NULL(主键不用设置,默认的是非空)
默认约束	DEFAULT	赋予某字段默认值,如该字段没有插入数据,则其值为默认值
唯一约束	UNIQUE KEY(UK)	设置字段的值是唯一的,允许为空,但只能有一个空值
主键约束	PRIMARY KEY(PK)	设置该字段为主键,可以作为表的唯一标识(不能重复),如学生的学号是唯一的
外键约束	FOREIGN KEY(FK)	设置该字段为外键,(存储引擎InnoDB支持,MyISAM不支持)
自增列	AUTO_INCREMENT	默认自增1,一般设置主键为自增列

常用的聚合函数:

AVG()	返回某字段的平均值
MAX()	返回某字段的最大植
MIN()	返回某字段的最小植
SUM()	返回某字段的和
COUNT()	返回某字段的行数,

字符串函数:

select concat('my','sql');返回mysql	连接字符串
insert('这是 Sql Server 数据库',3,10,'MySql'); 返回'这是MySql 数据库'	将字符串从索引为3的开始,10个长度的字符替换成MySql(包括空格)
select lower('ABC');返回'abc'	将字符串转为小写
select upper('abc');范湖'ABC'	将字符串转为大写
select substring('SQLServer',3,4);返回'LSer'	从第3位字符开始截取4个
select instr('abcd','c');返回3	返回c的下标
select length('ac');返回2	返回字符串或数字的字节长度,utf8的中文占3个字节
select char_length('abcd');返回4	返回字符串或数字的长度
select trim('   abc   ');返回'abc'	去掉两边的空格
select trim(both 'x' from'xxx我爱你xxx');返回'我爱你'	去掉两边的'x'
select trim(leading 'x' from 'xxx我爱你xxx');	去掉左边的'x'
select trim(trailing 'x' from 'xxx我爱你xxx'');	去掉右边的'x'
select replace('我爱你','爱','狠');返回'我恨你'	替换
select reverse('12345');返回54321	反转
select repeat('abc',3)返回'abcabcabc'	重复
select format(1234.1234,2);范湖1234.12	保留小数点后面的2位
select left('abcdefg',2);返回ab	截取左边的2个
select right('abcdefg',2)返回fg	截取右边2个
select space(3);返回3个空格	返回3个空格
select strcmp('a','z');	第一个字符串比第二个大就返回1,否则返回-1
select lpad('abc',2,'#');返回'##abc'	在字符串左边填充2个#
select rpad('abc',2,'#'');返回'abc##'	在字符串右边填充2个#

日期函数:

select curdate();返回 : 2019-01-01	获取当前日期
select curtime();返回 : 20:30:43	获取当前时间
select now();返回 : 2019-01-01 18:34:40	获取当前日期和时间
select week(date);返回: 26	返回日期date为一年中的几周
select year(date);返回: 2019	返回日期date的年份
select month(date);返回月	返回日期date的月份
select day(date);返回日	返回日期date的日
select hour(date):返回 9	返回时间date的小时值
select minute(date);返回 45	返回date的分钟值
select second(date);返回秒	返回date的秒
select datediff(date1,date2);返回2342	返回date1和date2之间相隔的天数
select adddate(date,n);返回 2019-01-01 18:23:12	返回日期date加上n天之后的日期和时间

数学函数:

select ceil(2.3);返回3	向上取整
select floor(2.3);返回2	向下取整
select rand();返回0-1的随机数 0.324234234432	0|1](包含1不包含0)

 事物:(BEGIN开始事务,COMMIT提交事务,FOLLBACK撤销事务)

(MySQL中默认开启自动提交,即未开启事务,每条SQL语句都是单独的事务执行完毕自动提交,可以关闭自动提交,手动提交或撤回事务)

设置自动提交开启和关闭SET=autocommit=0|1,值为0表示关闭,1表示开启 

子性
一致性
隔离性
持久性

/*是多行注释*/
# 这是单行注释
-- 这也是单行注释



/*
查看数据库的存储引擎(engine)
show variables like 'storage_engine%';
*/

/*
数据文件的存储位置(默认:c:/ProgramData/MySql/MySql Server 5.5/data/)
可在配置文件中my.ini中修改datadir=路径;
*/

/*
mysql用户的操作
[方括号里的代码表示可选]
*/

#创建新用户
CREATE USER 用户名@'localhost' [IDENTIFIED BY '密码'];
#删除用户(一般由数据库管理员来执行)
DROP USER 用户名@'localhost';
#给用户赋予权限
GRANT SELECT,INSERT(ALL表示所有的权限) ON 数据库.表(*表示所有表) TO 用户名@'localhost';
#创建用户同时赋予权限
GRANT 具体权限 ON 数据库.表 TO 用户名@'localhost' IDENTIFIED BY '密码';
#撤销权限
REVOKE 具体权限 ON 数据库.表 TO 用户名@'localhost';
#修改当前用户密码
SET PASSWORD=PASSWORD('新密码');
#修改其他用户密码(只有超级管理员才能修改)
SET PASSWORD FOR 用户名@'localshot'=PASSWORD('新密码');
#使用dos命令修改密码(只有超级管理员才能修改)
mysqladmin -u root -p PASSWORD "新密码"
#使用dos命令登录mysql
mysql -u root -p

/*
mysql数据库的操作
*/

#创建数据库
CREATE DATABASE 数据库名;
#删除数据库
DROP DATABASE 数据库名; 
#选择数据库
USE 数据库名;
#查看数据列表
SHOW DATABASES;

/*
mysql数据表的操作
*/

#创建数据表
CREATE TABLE [IF NOT EXISTS] 表名(字段名 数据类型 [属性|约束|索引|COMMENT '注释'])[COMMENT='表注释' CHARSET='utf8'];
#查看某个数据库的所有表(必须先选择数据库,否则报错)
SHOW TABLES;
#删除数据表
DROP TABLE [IF EXISTS] 表名;
#查看表结构(快捷键F6)
DESC 表名;

/*
mysql数据表结构的操作
*/

#修改表名
ALTER TABLE 原表名 RENAME TO 新表名;
#为表添加字段
ALTER TABLE 表名 ADD 字段名 数据类型 属性;
#删除字段
ALTER TABLE 表名 DROP 字段名;
#修改字段
ALTER TABLE 表名 CHANGE 原字段 新字段 数据类型 属性;
#添加主键约束
ALTER TABLE 表名 ADD CONSTRAINT 主键名(pk_表名) PRIMARY KEY(主键字段);
#添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名(fk_表名_关联表名) FOREIGN KEY(外键字段) REFERENCES 关联表名(关联字段);
#添加唯一约束
ALTER TABLE 表名 ADD UNIQUE KEY(字段名);
#修改表的字符集编码
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8;

/*
mysql表数据的增,删,改的操作
*/

#插入数据
INSERT INTO 表名[(列名)] VALUES(值列表);
#插入多行数据
INSERT INTO 表名[(列名)] VALUES(值列表),(值列表),(值列表);
#将原有表中的数据插入到新表(新表必须先存在)
INSERT INTO 表名[(列名)] SELECT 字段名 FROM 表名 [WHERE 条件];
#将原有表中的数据插入到新表(新表不能先存在)
CREATE TABLE 新表名(SELECT 字段名 FROM 表名 [WHERE 条件]);
#修改数据
UPDATE 表名 SET 字段1=值1,字段2=值2,字段3=值3 [WHERE 条件];
#删除数据
DELETE FROM 表名 [WHERE 条件];
#删除表中所有数据(重置自增列,表结构,字段,约束不变)
TRUNCATE TABLE 表名;

/*
mysql表数据的查询
*/

#简单的查询
SELECT 字段 FROM 表名<视图>
[WHERE<查询条件>]
[GROUP BY<分组的字段名>]
[HAVING<分组后的条件>]
[ORDER BY<排序的字段名>[ASC或DESC(默认是ASC升序)]]
[LIMIT[从第几行开始显示,第一条下表为0],[显示的行数]]

#表内连接查询
SELECT * FROM student stu
INNER JOIN result r ON r.studentNO=stu.StudentNO(两表之间关联条件)
INNER JOIN `subject` s ON s.subjectNO=r.subjectNO(两表之间关联条件)
WHERE studentResult=60 AND subjectName='Logic java'(查询条件)

#简单的子查询
SELECT 字段 FROM 表名 WHERE
age 运算符 (SELECT age FROM 表名 [WHERE])

#IN子查询              (子查询返回多个结果必须用IN)解释:查询student表中age中包含子查询返回的结果中的所有学生姓名
SELECT `name` FROM student WHERE age IN(子查询)
#NOT IN子查询          解释:查询student表中age中不包含子查询返回的结果中的所有学生姓名
SELECT `name` FROM student WHERE age NOT IN(子查询)

(后续完善)
#EXISTS 子查询
(后续完善)
#NOT EXISTS 子查询

/*
mysql视图操作
*/

#创建视图
CREATE VIEW 视图名 AS <select查询语句>
#删除视图
DROP VIEW [IF EXISTS] 视图名;
#查看视图
SELECT 字段 FROM 视图名;

/*
数据库的备份和恢复(dos窗口的操作)
*/

#备份数据
mysqldump -u root -p 数据库 [表名1,表名2] > d:/student.sql(路径/文件的名称.sql)
#恢复数据(方法1)
mysql -u root -p 数据库 < d:/myschool.sql(路径/文件名.sql)
#恢复数据(方法2)
mysql -u root -p(先登录服务器)
输入密码
use 数据库名
source d:/myschool.sql

/*
通过复制文件实现数据的备份和恢复
*/

#表数据导出到文本文件
SELECT * FROM student
INTO OUTFILE 'd:/student.txt'
#文本文件导入到数据表
LOAD DATE INFILE 'd:/studnet.txt' INTO student(表结构要先创建好);