只要是在客观世界存在的、可以被描述出来的都是实体
是一种系统软件,提供操作数据库的环境,可以通过数据库管理系统对数据进行插入、修改、删除和查询等操作。
结构化查询语言 专门用来和数据库进行交流的语言,几乎所有的DBMS都支持SQL
C:\Program Files\MySQL\MySQL Server 5.5\my.ini
net start MySQL
net stop MySQL
mysql -h 127.0.0.1 -P 3306 -uroot -p123456
exit
use test;
show tables;
show tables from mysql;
select database();
DESC user;
不同的字段需要设置为各种合适的类型,比如年龄就是整数类型
默认值是指如果用户没有指定值的情况下会记录的此字段指定一个提供一个预先设定的值
可以把居住地默认值设置为北京
我们可以指定某个字段不能不输入,必须提供一个非空的值
姓名字段不能为空
主键的选择标准
成绩表中的学生ID应该在学生表中是存在的 我们应该让成绩表中的ID只能引用学生表中的ID,它们的值应该是一一对应的,也就是说成绩表中的ID是成绩表中的外键,对应学生表的主键 ,这样就可以保证数据的引用完整性
唯一约束是指某个字段值是唯一的,在所有的记录中不能有重复的值.
学生的身份证号可以设置为唯一约束
一个表的外键必须引用另一个表的主键,比如成绩表中的学生ID会引用学生表的主键,课程ID会引用成绩表的主键
CREATE TABLE `student` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
`name` varchar(50) NOT NULL ,
`age` int(11) NULL DEFAULT NULL ,
`city` varchar(50) DEFAULT '北京' ,
)
ALTER TABLE `student` ADD COLUMN `idcard` varchar(15) NULL AFTER `city`; --增加身份证字段
ALTER TABLE `student` MODIFY COLUMN `idcard` varchar(18) DEFAULT NULL AFTER `name`; --修改身份证字段
ALTER TABLE `student`DROP COLUMN `idcard`; --删除身份证字段
-- 主键约束
ALTER TABLE `student` ADD PRIMARY KEY (`id`);
-- 唯一约束
ALTER TABLE `student` ADD UNIQUE INDEX `uq_idcard` (`idcard`) ;
-- 默认约束
ALTER TABLE `student` MODIFY COLUMN `city` varchar(50) DEFAULT '北京' AFTER `age`;
-- 外键约束
ALTER TABLE `score` ADD CONSTRAINT `fk_stuid` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`);
-- 删除约束
ALTER TABLE `score` DROP FOREIGN KEY `fk_stuid`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`idcard` varchar(18) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`city` varchar(50) DEFAULT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `score` (
`student_id` int(11) NOT NULL DEFAULT '0',
`course_id` int(11) NOT NULL DEFAULT '0',
`grade` float DEFAULT NULL,
PRIMARY KEY (`student_id`,`course_id`),
KEY `fk_courseid` (`course_id`),
CONSTRAINT `fk_courseid` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`),
CONSTRAINT `fk_stuid` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Structured Query Language:结构化查询语言
主要的命令有CREATE
、ALTER
、DROP
等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用
它们是SELECT
、UPDATE、
INSERT、
DELETE`,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
是用来设置或更改数据库用户或角色权限的语句,包括(grant
,revoke
等)语句
是一种符号,它是用来进行列间或者变量之间的比较和数学运算的
运算符 | 说明 |
---|---|
+ | 加运算,求两个数或表达式相加的和,如1+1 |
- | 减少减运算,求两个数或表达式相减的差,如4-1 |
* | 乘运算,求两个数或表达式相乘的积,如2*2 |
/ | 除运算,求两个数或表达式相除的商,如6/4的值为1 |
% | 取模运算,求两个数或表达式相除的余数,如:6%4的值为2 |
运算符 | 说明 |
---|---|
AND | 当且仅当两个布尔表达式都为true时,返回TRUE |
OR | 当且仅当两个布尔表达式都为false,返回FALSE |
NOT | 布尔表达式的值取反 |
运算符 | 说明 |
---|---|
= | 等于 |
> | 大于 |
< | 小于 |
<> | 不等于 |
>= | 大于等于 |
<= | 小于等于 |
!= | 不等于 |
INSERT [INTO] 表名 [(列名)] VALUES (值列表)
向学生表插入一条记录, 姓名张三,身份证号123456,年龄30,城市北京
INSERT INTO [school].[student]
(name,idcard,age,city)
VALUES
('张三','123456',30,'北京')
UPDATE 表名 SET 列名 = 更新值 [WHERE 更新条件]
UPDATE [school].[student]
SET age = 40,city = '上海'
WHERE id=7
id=7 and idcard='410787'
email is null or email = ''
DELETE [FROM] 表名 [WHERE <删除条件>]
DELETE FROM [school].[student] WHERE id=7
截断整个表中的数据
TRUNCATE TABLE 表名
TRUNCATE TABLE student
数据全部清空,但表结构、列、约束等不被改动 不能用于有外键约束引用的表 标识列重新开始编号 因为要删除的数据不会写入日志,数据也不能恢复,所以工作中请尽量不要使用此命令
SELECT <列名>
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名>[ASC或DESC]]
SELECT id,name,idcard,age,city
FROM student
WHERE home= '北京'
ORDER BY id asc
SELECT id,name,idcard,age,city AS home
FROM student
WHERE city= '山东'
ORDER BY id asc
SELECT id,name,age,city
FROM student
WHERE city is null or city =''
SELECT id,name,age,city,'中国' as country
FROM student
SELECT id,name,age,city,'中国' as country
FROM student limit 2
SELECT id,name,age,DISTINCT city,'中国' as country
FROM student
SELECT 1+1
SELECT 1+'1'
SELECT 1+'zfpx'
SELECT 1+null
SELECT CONCAT(last_name,first_name) FROM user;
ALTER TABLE `student`
ADD COLUMN `province` varchar(50) NULL AFTER `city`,
ADD COLUMN `birthday` date NULL AFTER `province`,
ADD COLUMN `gender` int(11) NULL AFTER `birthday`,
ADD COLUMN `email` varchar(50) NULL AFTER `gender`;
INSERT INTO `student`(id,name,gender,age,city,province,birthday,idcard,email) VALUES ('1', '郭靖', '1', '1', '济南', '山东省', '1982-09-03', 1, '1@qq.com');
INSERT INTO `student`(id,name,gender,age,city,province,birthday,idcard,email) VALUES ('2', '黄蓉', '2', '2', '济南', '山东省', '1982-09-03', 0, '2@qq.com');
INSERT INTO `student`(id,name,gender,age,city,province,birthday,idcard,email) VALUES ('3', '杨过', '3', '3', '终南山', '陕西省', '1979-09-03', 1, '3@qq.com');
INSERT INTO `student`(id,name,gender,age,city,province,birthday,idcard,email) VALUES ('4', '小龙女', '4', '4', '终南山', '陕西省', '1970-09-03', 0, '4@qq.com');
INSERT INTO `student`(id,name,gender,age,city,province,birthday,idcard,email) VALUES ('5', '欧阳锋', '5', '5', '白驼山', '新疆', '1989-09-09', 1, '5@qq.com');
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');
INSERT INTO `course` VALUES ('3', '英语');
INSERT INTO `score` VALUES ('1', '1', '100');
INSERT INTO `score` VALUES ('1', '2', '90');
INSERT INTO `score` VALUES ('1', '3', '70');
INSERT INTO `score` VALUES ('2', '1', '100');
INSERT INTO `score` VALUES ('2', '2', '90');
INSERT INTO `score` VALUES ('2', '3', '80');
INSERT INTO `score` VALUES ('3', '1', '100');
INSERT INTO `score` VALUES ('3', '2', '90');
INSERT INTO `score` VALUES ('3', '3', '80');
select * from student where province = '山东';
select * from student where province = '山东' and gender=1;
SELECT name,idcard FROM student WHERE email IS NULL
select * from score order by grade asc;
select * from score order by grade desc;
select * from score order by course_id asc, grade desc;
函数名称 | 描述 |
---|---|
CONCAT | 字符串连接 |
CONCAT_WS | 使用指定的分隔符进行字符连接 |
FORMAT | 数字格式化 |
LOWER | 转小写字母 |
UPPER | 转大写字母 |
LEFT | 返回字符串s开始的最左边n个字符 |
RIGHT | 返回字符串s开始的最左边n个字符 |
SELECT LENGTH('zfpx');
SELECT LENGTH('珠峰培训');
SHOW VARIABLES like '%char%';
SELECT CONCAT(last_name,'_',first_name) FROM employees;
SELECT UPPER('zfpx') FROM employees;
SELECT LOWER('ZFPX') FROM employees;
SELECT SUBSTR('zfpx',2);-- 截取从指定索引处开始的所有的字符
SELECT SUBSTR('zfpx',2,3);--截取从指定索引处开始的指定数量的字符
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)));
SELECT INSTR('zfpx','f');
SELECT TRIM(' zfpx ');
SELECT LTRIM(' zfpx ');
SELECT RTRIM(' zfpx ');
SELECT TRIM('x' FROM 'xxzfpxxx');
SELECT LPAD('zfpx',10,'@');
SELECT LPAD('1',8,'0');
SELECT RPAD('1',8,'0');
SELECT REPLACE('zfpx','f','q')
SELECT FORMAT(100000,2); 100,000.00
select format(100.31111,2) 100.31
select format(100.31111,0); 100
SELECT FORMAT(423423234.65534453,2); 423,423,234.66
select left('abcde12345',5); //abcde
select left('abcde12345',5); //12345
函数名称 | 描述 |
---|---|
CEIL | 向上取整 |
FLOOR | 向下取整数 |
DIV | 整数取 |
MOD | 取余(取模) |
POWER | 幂运算 |
ROUND | 四舍五入 |
TRUNCATE | 数字截取 |
SELECT ROUND(2.5);
SELECT ROUND(2.555,2);
SELECT CEIL(1.00);
SELECT FLOOR(1.00);
SELECT TRUNCATE(1.66,1);
SELECT mod(10,3);
函数名称 | 描述 |
---|---|
NOW | 当前日期和时间 |
CURDATE | 当前日期 |
CURTIME | 当前时间 |
DATE_ADD | 日期变化 |
DATEDIFF | 计算日期差 |
DATE_FORMAT | 日期格式化 |
SELECT NOW():
SELECT CURDATE():
SELECT CURTIME():
年 月 日 小时 分钟 秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
将日期格式的字符串转成指定格式的日期
SELECT STR_TO_DATE('2018-09-09','%Y-%m-%d')
序号 | 格式符 | 功能 |
---|---|---|
1 | %Y | 4位的年份 |
2 | %y | 2位的年份 |
3 | %m | 月份(01,02) |
4 | %c | 月份(1,2) |
5 | %d | 日(01,02) |
6 | %H | 小时(24小时制) |
7 | %h | 小时(12小时制) |
8 | %i | 分钟(00,01) |
9 | %s | 秒(00,01) |
查询入职日期为1984-8-4的员工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('12-30 1984','%m-%d %Y');
将日期转换成指定字符串
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')
SELECT DATE_ADD(NOW(),INTERVAL 365 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR);
SELECT DATEDIFF('2019-1-1',NOW());
SELECT CONNECTION_ID();
SELECT DATABASE();
SELECT VERSION();
select LAST_INSERT_ID();
SELECT USER();
SELECT MD5('123456');//摘要算法
SELECT PASSWORD('123456');//修改当前用户的密码
SELECT User,Password from mysql.user;
SELECT IF(1>0,'A','B');
CASE 要判断的字段和表达式
WHEN 常量1 then 要显示的值或语句1
WHEN 常量2 then 要显示的值或语句2
ELSE 要显示的值
END
SELECT
CASE
WHEN grade<60 then '不及格'
WHEN grade>=60 then '及格'
ELSE '未知'
END
FROM score;
SELECT
CASE level
WHEN 'A' then '优秀'
WHEN 'B' then '良好'
ELSE '未知'
END
FROM score;
CREATE FUNCTION func_name() RETURNS VARCHAR(64)
body
CREATE FUNCTION znow() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分%s秒');
CREATE FUNCTION zadd(num1 INT,num2 INT) RETURNS INT
RETURN num1+num2;
CREATE TABLE stu(id int PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50));
CREATE FUNCTION addUser(name VARCHAR(50)) RETURNS INT
BEGIN
INSERT INTO stu(name) VALUES(name);
RETURN LAST_INSERT_ID();
END
SELECT addUser('zfpx');
DROP FUNCTION addUser
就是查询的条件是模糊的,不是特别明确的
代替一个或多个真正的字符,与LIKE 关键字一起使用
_
表示一个任意字符 %
表示任意长度的字符串查询某一列在指定的规范内的记录,包括两个边界
select * from score where grade between 80 and 100
查询某一列中的值在列出的内容列表中
select * from student where city in ('北京','上海','广东')
CREATE USER 用户名@访问地址 IDENTIFIED BY '密码'
CREATE USER user1@localhost IDENTIFIED BY '123456'
GRANT 权限1,权限2,,, ON 数据库.* TO 用户名
GRANT CREATE,DROP ON db1.* TO user1@localhost;
REVOKE 权限1,权限2,,,ON 数据库.* FROM 用户名;
REVOKE CREATE,DROP ONT db1.* FROM user1@localhost;
SHOW GRANTS FROM 用户名
SHOW GRANTS FROM user1@localhost;
DROP USER 用户名
DROP USER user1@localhost;
UPDATE USER SET PASSWORD=PASSWORD('password') WHERE User='username' and Host='localhost';
FLUSH PRIVILEGES;
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])
CALL Avg_Price()
DROP PROCEDURE IF EXISTS Avg_Price;
create procedure sum (in a int,in b int,out result int)
begin
select a+b INTO result;
end
call sum5(1,2,@result);
select @result;
-- 构建一个8388608条记录的数据
-- 创建用户表
create table user(
id int,
username varchar(64),
userno int
)
-- 创建生成随机字符串的函数
CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255)
BEGIN
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END;
-- 产生随机数字
create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end
-- 创建存储过程,向用户表添加海量数据
create procedure insert_user(in start int(10),in max_num int(10))
begin
declare i int;
set i=0;
while i<max_num do
set i=i+1;
insert into user(id,username,userno) values((start+i),rand_string(6),rand_num());
end while;
end;
call insert_user(1,8388608);
create table user(id int primary key,name varchar(32),email varchar(32));
alter table user add index(name);
create index idx_name on user(name);
show index from 表名;
show index from user
alter table 表名 drop primary;
alter table 表名 drop index 索引名;
drop index 索引名 on 表名;
explain
select * from users where userno=4593;
alter table users add index(userno);