MySql操作
来源：互联网 发布：淘宝账号为什么会被盗 编辑：程序博客网 时间：2024/06/01 08:24
/***************************用户**********************************/
select user(); ---查看当前用户；
create user abc identified by '123456';---创建用户，查看navicat
创建用户，权限
grant select,insert,update,delete on *.* to 用户名 @'%' identified by '123456‘；
grant select,insert,update,delete on *.* to abc @'%' identified by '123456‘
所有权限
grant all privileges on *.* to username@'%'
grant all privileges on *.* to abc@'%'
回收权限
revoke all privileges on *.* from username@‘%‘
revoke all privileges on *.* from abc@‘%‘
修改密码：
admin>mysqladmin -uroot -p123456 password 654321 将密码修改成654321
查看用户：
select * from mysql.user;
select user from mysql.user;
删除用户abc；
drop user abc;
修改密码
admin>mysqladmin -u用户名 -p旧密码 password 新密码
admin>mysqladmin -uroot -p123456 password 654321 -----密码修改成654321
查看有几个用户
select user from mysql.user;
/*****************************************************************************/
/*****************************数据库操作**************************************/
1、mysql -uroot -p //登录数据库
2、show databases; //显示所有数据库
3、create database database_name character set character_name;//创建数据库
create database database_name character set gbk;
4、alter database database_name character set utf8; //字符集改为utf8
set name utf8;
5、drop database database_name; //删除数据库
/*****************************************************************************/
/*****************************数据表操作**************************************/
6、create table productinfo (
id INT,
proname VARCHAR(20),
proprice FLOAT(5,2),
prodate DATETIME
); //建数据表
7、alter table table_name
ADD column_name | MODIFY column_name | DROP COLUMN column_name//修改表的操作
alter table productinfo ADD proquantity int;//添加列
alter table productinfo MODIFY proname VARCHAR(30);//修改列宽
alter table productinfo CHANGE proname pronamenew VARCHAR(30);//修改列名
alter table productinfo DROP COLUMN proquantity;//删除列
8、DROP TABLE table_name; //删除数据表
DROP TABLE productinfo;
9、desc productinfo； //显示表的结构
/*****************************************************************************/
/*****************************主键约束**************************************/
1、创建表时设置主键约束
(1)使用SQL语句设置单一列约束
a、设置列级主键约束
create table customerInfo(
id INT PRIMARY KEY,
name VARCHAR(20),
age INT
);
b、设置表级主键约束
create table customerInfo(
id INT,
name VARCHAR(20),
age INT,
PRIMARY KEY(id)
);
(2)在创建表时设置联合主键
a、设置列级主键约束
create table customerInfo(
id INT PRIMARY KEY,
name VARCHAR(20) PRIMARY KEY,
age INT
);
b、设置表级主键约束
create table customerInfo(
id INT,
name VARCHAR(20),
age INT,
PRIMARY KEY(id， name)
);
2、在修改表时添加主键约束
(1)在修改表时设置单一列约束
alter table table_name
ADD CONSTRAINT pk_name PRIMARY KEY;
alter table customerInfo ADD CONSTRAINT PK_customerInfo PRIMARY KEY(age);
(2)在修改表时设置联合主键
alter table table_name
ADD CONSTRAINT PK_name PRIMARY KEY();
alter table customerInfo ADD CONSTRAINT PK_customerInfo PRIMARY KEY(id, age);
3、删除主键
alter table table_name DROP PRIMARY KEY;
/*****************************************************************************/
/*****************************外键约束**************************************/
1、创建表时设置外键约束
create table TypeInfo(roomtypeId INT PRIMARY KEY, roomtype VARCHAR(20));
create table RoomInfo(
roomId INT PRIMARY KEY,
roomtypeId INT,
roomname VARCHAR(30),
CONSTRAINT FK_RoomInfo FOREIGN KEY(roomId) REFERENCES TypeInfo(roomtypeId)
);
2、修改表时添加外键约束
alter table table_name1 ADD CONSTRAINT fk_name FOREIGN KEY(列1)REFERENCES table_name2(列2);
alter table RoomInfo ADD CONSTRAINT FK_RoomInfo FOREIGN KEY(roomtypeId) REFERENCES TypeInfo(roomtypeId);
3、删除外键
alter table table_name DROP FOREIGN KEY FK_NAME;
alter table RoomInfo DROP FOREIGN KEY FK_RoomInfo;
/*****************************************************************************/
/*****************************默认值约束**************************************/
1、创建表时设置默认值约束
create table table_name(id int DEFAULT 0, name VARCHAR(20));//(0为默认值)
2、修改表时添加默认值约束
alter table table_name alter id set 2;
3、删除默认值
alter table table_name alter id DROP DEFAULT;
/*****************************************************************************/
/*****************************非空约束**************************************/
1、创建表时设置非空约束
create table table_name(id int NOT NULL, name VARCHAR(20));
2、修改表时添加非空约束
alter table table_name MODIFY name VARCHAR(20) NOT NULL;
/*****************************************************************************/
/*****************************检查约束**************************************/
1、创建表时设置检查约束
create table table_name(age int CHECK(age >= 18), name VARCHAR(20));
2、修改表时添加检查约束
alter table table_name ADD CONSTRAINT UQ_age CHECK(age >= 20);
注意：mysql中虽然可以设置检查约束，但是检查约束在表中是不生效的，仍然可以插入不符合条件的数据。
/*****************************************************************************/
/*****************************唯一约束**************************************/
1、创建表时设置唯一约束
create table table_name(age int, name VARCHAR(20) UNIQUE);
create table table_name(age int, name VARCHAR(20), UNIQUE(name));
共同唯一约束
create table table_name(age INT, name VARCHAR(20), UNIQUE(age, name));
2、修改表时添加唯一约束
alter table table_name ADD CONSTRAINT UQ_name UNIQUE(name);
3、删除唯一约束
DROP INDEX UQ_name ON table_name;
/*****************************************************************************/
/*****************************DML语言操作数据表**************************************/
一、insert语句
1、insert value形式
insert into table_name value(1, '小明');
2、insert set形式
insert into table_name set id = 2, name = '小红';
3、insert select形式——复制其他表数据
create table table_name1(id int, name VARCHAR(30));
insert into table_name1 value(10, '老王');
insert into table_name select * from table_name1;
4、添加多条数据
insert into table_name value(3, '小张'), (4, '小李');
二、update语句
1、根据条件修改表中的数据
update table_name set name = '小韩' where id = 1;
2、根据顺序修改表中的数据
update table_name set ORDER BY id desc/asc;(降序/升序)
3、限制行数的修改
update table_name set name = 'Naic' where id >= 10 LIMIT 2;
四、delete语句
1、删除表中所有数据
detele from table_name;
truncate table table_name;
2、根据条件删除数据
delete from table_name where id = 1;
3、按指定顺序删除数据
delete from table_name order by id desc;
4、限制行数的删除
delete from table_name order by id desc LIMIT 1;
/*****************************************************************************/
/*****************************简单查询**************************************/
1、查询表中的全部数据
select * from table_name;
2、查询指定字段的数据
select id, name from table_name;
3、在查询中使用别名列
select id as '编号', name as '姓名' from table_name;
4、根据单一条件查询数据
select * from table_name where id = 1;
5、带like条件的查询
select * from table_name where name like '%老%';
6、根据多个条件查询数据
select * from table_name where id = 1 and(or) name = '小明';
7、对查询结果进行排序
select * from table_name ORDER BY id desc(asc);
8、限制查询结果行数
select * from table_name LIMIT 2;
/*****************************************************************************/
/*****************************聚合函数**************************************/
1、最大值函数MAX
select MAX(id) from table_name;
2、最小值函数MIN
select MIN(id) from table_name;
3、平均值函数AVG
select AVG(id) from table_name;
4、求和函数SUM
select SUM(id) as '总和' from table_name;
5、计数函数COUNT COUNT只计算非NULL值，COUNT(*)都计算
select COUNT(id) as '人数' from table_name;
/*****************************************************************************/
/*****************************子查询**************************************/
1、使用IN的子查询 name包含于(select name from table_name1)
select * from table_name where name IN(select name from table_name1);
2、使用EXIST的子查询 where true(false)true打印 false无结果
select * from table_name where EXIST (select * from table_name1 where name = 'mike');
3、使用ANY的子查询 name 包含于ANY(select name from table_name1) 返回true
select * from table_name where name = ANY(select name from table_name1);
/*****************************************************************************/
/*****************************分组查询**************************************/
create table studentInfo(id int PRIMARY KEY, name VARCHAR(20), score decimal(4,2), subject VARCHAR(20), teacher VARCHAR(20));
1、对单列进行分组查询
select subject, COUNT(*) from studentInfo GROUP BY subject;
2、使用HAVING的分组查询
select subject, AVG(score) from studentInfo GROUP BY subject HAVING subject = '数学';
3、对多列进行分组查询
select subject, teacher, AVG(score) from studentInfo GROUP BY subject, teacher;
4、在分组查询中使用ORDER BY
select teacher, SUM(score) from studentInfo GROUP BY teacher ORDER BY sum(score) desc;
/*****************************************************************************/
/*****************************多表查询**************************************/
create table newInfo(id int PRIMARY KEY, name VARCHAR(20), score decimal(4,2), subjectid int, teacherid int);
create table subjectInfo(id int PRIMARY KEY, subjectname VARCHAR(30));
create table teacherInfo(id int PRIMARY KEY, teachername VARCHAR(30));
1、等值连接
mysql> select newInfo.name,subjectInfo.subjectname from newInfo,subjectInfo where newInfo.subjectid=subjectInfo.id;
+--------+-------------+
| name | subjectname |
+--------+-------------+
| 小明 | 数学 |
| 小红 | 英语 |
| 小李 | 语文 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select newInfo.name,subjectInfo.subjectname,teacherInfo.teachername from newInfo,subjectInfo,teacherInfo where newInfo.subjectid = subjectInfo.id and newInfo.teacherid = teacherInfo.id;
+--------+-------------+-------------+
| name | subjectname | teachername |
+--------+-------------+-------------+
| 小明 | 数学 | 张老师 |
| 小红 | 英语 | 王老师 |
| 小李 | 语文 | 吴老师 |
+--------+-------------+-------------+
3 rows in set (0.00 sec)
2、笛卡尔积 3*4列和5*7列表不加条件查询，得到8*28列表
select * from newInfo,subjectInfo,teacherInfo;
查询所得结果非常庞大，并且非常臃肿，对实际的应用没有什么太大的意义，在实际应用中要加指定查询条件，避免笛卡尔积的出现
3、外连接
(1)左外连接
mysql> select newInfo.name,subjectInfo.subjectname from newInfo LEFT OUTER JOIN subjectInfo ON newInfo.subjectid = subjectInfo.id;
+--------+-------------+
| name | subjectname |
+--------+-------------+
| 小明 | 数学 |
| 小红 | 英语 |
| 小李 | 语文 |
| 小胡 | NULL |
+--------+-------------+
4 rows in set (0.00 sec)
(2)右外连接
mysql> select newInfo.name,subjectInfo.subjectname from newInfo RIGHT OUTER JOIN subjectInfo ON newInfo.subjectid = subjectInfo.id;
+--------+-------------+
| name | subjectname |
+--------+-------------+
| 小李 | 语文 |
| 小明 | 数学 |
| 小红 | 英语 |
| NULL | 计算机 |
+--------+-------------+
4 rows in set (0.00 sec)
4、内连接 相当于等值连接 相比较更加明显是多表查询
mysql> select newInfo.name,subjectInfo.subjectname from newInfo INNER JOIN subjectInfo ON newInfo.subjectid = subjectInfo.id;
+--------+-------------+
| name | subjectname |
+--------+-------------+
| 小李 | 语文 |
| 小明 | 数学 |
| 小红 | 英语 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select newInfo.name,subjectInfo.subjectname,teacherInfo.teachername from newInfo INNER JOIN subjectInfo INNER JOIN teacherInfo ON newInfo.subjectid=subjectInfo.id and newInfo.teacherid=teacherInfo.id;
+--------+-------------+-------------+
| name | subjectname | teachername |
+--------+-------------+-------------+
| 小明 | 数学 | 张老师 |
| 小红 | 英语 | 王老师 |
| 小李 | 语文 | 吴老师 |
+--------+-------------+-------------+
3 rows in set (0.00 sec)
/*****************************************************************************/
/*****************************合并查询结果**************************************/
1、使用UNION关键字合并查询结果（多表查询的数据表） 两张表的列数相同，数据类型相同
mysql> select * from teacherInfo
-> UNION
-> select * from subjectInfo;
+----+-------------+
| id | teachername |
+----+-------------+
| 1 | 张老师 |
| 2 | 王老师 |
| 3 | 吴老师 |
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 计算机 |
+----+-------------+
7 rows in set (0.00 sec)
2、对合并后的查询结果排序
mysql> select * from teacherInfo
-> UNION
-> select * from subjectInfo
-> ORDER BY id;
+----+-------------+
| id | teachername |
+----+-------------+
| 1 | 张老师 |
| 1 | 语文 |
| 2 | 数学 |
| 2 | 王老师 |
| 3 | 英语 |
| 3 | 吴老师 |
| 4 | 计算机 |
+----+-------------+
7 rows in set (0.00 sec)
3、限制组合查询结果的行数
mysql> select * from teacherInfo
-> UNION
-> select * from subjectInfo
-> LIMIT 3;
+----+-------------+
| id | teachername |
+----+-------------+
| 1 | 张老师 |
| 2 | 王老师 |
| 3 | 吴老师 |
+----+-------------+
3 rows in set (0.00 sec)
/*****************************************************************************/
/*****************************函数**************************************/
1、绝对值函数abs
mysql> select abs(-21), abs(21), abs('-1'), abs(-91/4);
+----------+---------+-----------+------------+
| abs(-21) | abs(21) | abs('-1') | abs(-91/4) |
+----------+---------+-----------+------------+
| 21 | 21 | 1 | 22.7500 |
+----------+---------+-----------+------------+
1 row in set (0.02 sec)
2、取余函数mod
mysql> select mod(10, 3), mod('10', 3), mod(10,5/3), mod(-10, 2), mod(1, 0);
+------------+--------------+-------------+-------------+-----------+
| mod(10, 3) | mod('10', 3) | mod(10,5/3) | mod(-10, 2) | mod(1, 0) |
+------------+--------------+-------------+-------------+-----------+
| 1 | 1 | 0.0000 | 0 | NULL |
+------------+--------------+-------------+-------------+-----------+
1 row in set (0.00 sec)
3、求平方根函数sqrt
mysql> select sqrt(-100), sqrt(100), sqrt(100.89), sqrt('100.89');
+------------+-----------+------------------+------------------+
| sqrt(-100) | sqrt(100) | sqrt(100.89) | sqrt('100.89') |
+------------+-----------+------------------+------------------+
| NULL | 10 | 10.0444014256699 | 10.0444014256699 |
+------------+-----------+------------------+------------------+
1 row in set (0.00 sec)
4、获取随机数函数rand 可返回一个随机浮点值 0~1.0 带参数作为种子值
mysql> select rand(), rand(2), rand(5.1);
+-------------------+-------------------+-------------------+
| rand() | rand(2) | rand(5.1) |
+-------------------+-------------------+-------------------+
| 0.967204678773139 | 0.655586646549019 | 0.406135974830143 |
+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)
5、四舍五入函数round
mysql> select round(6.54321, 2), round(6.54321, 2.88), round(6.54321, -1);
+-------------------+----------------------+--------------------+
| round(6.54321, 2) | round(6.54321, 2.88) | round(6.54321, -1) |
+-------------------+----------------------+--------------------+
| 6.54 | 6.543 | 10 |
+-------------------+----------------------+--------------------+
1 row in set (0.00 sec)
6、符号函数sign 正数返1,0返0，负数返-1
mysql> select sign(21), sign('321'), sign(-1), sign(0.00),sign(-1*'1');
+----------+-------------+----------+------------+--------------+
| sign(21) | sign('321') | sign(-1) | sign(0.00) | sign(-1*'1') |
+----------+-------------+----------+------------+--------------+
| 1 | 1 | -1 | 0 | -1 |
+----------+-------------+----------+------------+--------------+
1 row in set (0.00 sec)
7、幂运算函数power
mysql> select power(8,2), power('8', 3), power(2.1, 2.5), power(-2, 2), power(-1,2.2);
+------------+---------------+------------------+--------------+---------------+
| power(8,2) | power('8', 3) | power(2.1, 2.5) | power(-2, 2) | power(-1,2.2) |
+------------+---------------+------------------+--------------+---------------+
| 64 | 512 | 6.39069714506954 | 4 | NULL |
+------------+---------------+------------------+--------------+---------------+
1 row in set (0.00 sec)
8、对数运算函数log 无参代表ln()
mysql> select log(5, 25), log(3, '9'), log(2), log(-2);
+------------+-------------+-------------------+---------+
| log(5, 25) | log(3, '9') | log(2) | log(-2) |
+------------+-------------+-------------------+---------+
| 2 | 2 | 0.693147180559945 | NULL |
+------------+-------------+-------------------+---------+
1 row in set (0.00 sec)
9、pi函数
mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
10、三角函数
mysql> select sin(pi()/2), cos(pi()), tan(pi()/4);
+-------------+-----------+-------------+
| sin(pi()/2) | cos(pi()) | tan(pi()/4) |
+-------------+-----------+-------------+
| 1 | -1 | 1 |
+-------------+-----------+-------------+
1 row in set (0.00 sec)
11、获取最小整数ceil、ceiling
mysql> select ceiling(-234.32), ceil(-234.32), ceiling(234.32), ceil(234.32);
+------------------+---------------+-----------------+--------------+
| ceiling(-234.32) | ceil(-234.32) | ceiling(234.32) | ceil(234.32) |
+------------------+---------------+-----------------+--------------+
| -234 | -234 | 235 | 235 |
+------------------+---------------+-----------------+--------------+
1 row in set (0.00 sec)
12、合并字符串函数concat
mysql> select concat('this','a','test'),concat('this', 'a', 'test',null),concat(123, 456);
+---------------------------+----------------------------------+------------------+
| concat('this','a','test') | concat('this', 'a', 'test',null) | concat(123, 456) |
+---------------------------+----------------------------------+------------------+
| thisatest | NULL | 123456 |
+---------------------------+----------------------------------+------------------+
1 row in set (0.00 sec)
13、计算字符串长度函数length char_length()以字符为单位，多字节字符当成一个字符，length()以字节为单位
mysql> select char_length('1234'),char_length('测试'), length('测试');
+---------------------+-----------------------+------------------+
| char_length('1234') | char_length('测试') | length('测试') |
+---------------------+-----------------------+------------------+
| 4 | 2 | 6 |
+---------------------+-----------------------+------------------+
1 row in set (0.00 sec)
14、字母小写转大写upper
mysql> select upper('abcd测试'), ucase('abcd测试');
+---------------------+---------------------+
| upper('abcd测试') | ucase('abcd测试') |
+---------------------+---------------------+
| ABCD测试 | ABCD测试 |
+---------------------+---------------------+
1 row in set (0.00 sec)
15、字母大写转小写lower
mysql> select lower('ABCD测试'), lcase('ABCD测试');
+---------------------+---------------------+
| lower('ABCD测试') | lcase('ABCD测试') |
+---------------------+---------------------+
| abcd测试 | abcd测试 |
+---------------------+---------------------+
1 row in set (0.00 sec)
16、获取指定长度的字符串的函数left和right
mysql> select left('left测试函数', 5), right('right测试函数', 2);
+-----------------------------+-------------------------------+
| left('left测试函数', 5) | right('right测试函数', 2) |
+-----------------------------+-------------------------------+
| left测 | 函数 |
+-----------------------------+-------------------------------+
1 row in set (0.00 sec)
17、填充字符串函数lpad和rpad
mysql> select lpad('测试函数', 8, 'lpad'),rpad('测试函数',8,'rpad'),rpad('测试函数',2,'MYSQL');
+---------------------------------+-------------------------------+--------------------------------+
| lpad('测试函数', 8, 'lpad') | rpad('测试函数',8,'rpad') | rpad('测试函数',2,'MYSQL') |
+---------------------------------+-------------------------------+--------------------------------+
| lpad测试函数 | 测试函数rpad | 测试 |
+---------------------------------+-------------------------------+--------------------------------+
1 row in set (0.00 sec)
18、删除指定字符函数trim both两端删除 leading前端删除 trailing后端删除 无参删两端空格
mysql> select trim(trailing 'm' from 'msqltrim'), trim(both 'm' from 'mysqltrim'), trim(' mysqltrim ');
+------------------------------------+---------------------------------+---------------------+
| trim(trailing 'm' from 'msqltrim') | trim(both 'm' from 'mysqltrim') | trim(' mysqltrim ') |
+------------------------------------+---------------------------------+---------------------+
| msqltri | ysqltri | mysqltrim |
+------------------------------------+---------------------------------+---------------------+
19、删除两端空格函数ltrim，rtrim
mysql> select length(' mysql'), length('mysql '), length(ltrim(' mysql')), length(rtrim('mysql '));
+-------------------+-------------------+--------------------------+--------------------------+
| length(' mysql') | length('mysql ') | length(ltrim(' mysql')) | length(rtrim('mysql ')) |
+-------------------+-------------------+--------------------------+--------------------------+
| 7 | 7 | 5 | 5 |
+-------------------+-------------------+--------------------------+--------------------------+
1 row in set (0.00 sec)
20、重复生成字符串的函数repeat
mysql> select repeat('测试',3),repeat('测试',-1), repeat('测试',null);
+--------------------+---------------------+-----------------------+
| repeat('测试',3) | repeat('测试',-1) | repeat('测试',null) |
+--------------------+---------------------+-----------------------+
| 测试测试测试 | | NULL |
+--------------------+---------------------+-----------------------+
1 row in set (0.00 sec)
21、空格函数space
mysql> select length(space(10));
+-------------------+
| length(space(10)) |
+-------------------+
| 10 |
+-------------------+
1 row in set (0.00 sec)
22、替换函数replace
mysql> select replace('this is test', 't', 'T');
+-----------------------------------+
| replace('this is test', 't', 'T') |
+-----------------------------------+
| This is TesT |
+-----------------------------------+
1 row in set (0.00 sec)
23、替换字符串的函数insert
mysql> select insert('testinsert',2,2,'uu'),insert('testinsert',2,5,'uu'),insert('testinsert',2,1,'uuu');
+-------------------------------+-------------------------------+--------------------------------+
| insert('testinsert',2,2,'uu') | insert('testinsert',2,5,'uu') | insert('testinsert',2,1,'uuu') |
+-------------------------------+-------------------------------+--------------------------------+
| tuutinsert | tuusert | tuuustinsert |
+-------------------------------+-------------------------------+--------------------------------+
1 row in set (0.00 sec)
24、比较字符串大小的函数strcmp
mysql> select strcmp('strcmptest','strcmptest'),strcmp('stacmp','strcmp'),strcmp('strcmp','stacmp');
+-----------------------------------+---------------------------+---------------------------+
| strcmp('strcmptest','strcmptest') | strcmp('stacmp','strcmp') | strcmp('strcmp','stacmp') |
+-----------------------------------+---------------------------+---------------------------+
| 0 | -1 | 1 |
+-----------------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)
25、获取子串函数substring
mysql> select substring('substringtest',4,3),substring('substringtest',4);
+--------------------------------+------------------------------+
| substring('substringtest',4,3) | substring('substringtest',4) |
+--------------------------------+------------------------------+
| str | stringtest |
+--------------------------------+------------------------------+
1 row in set (0.00 sec)
26、字符串逆序函数reverse
mysql> select reverse('abcdefg'),reverse('学习MYSQL');
+--------------------+------------------------+
| reverse('abcdefg') | reverse('学习MYSQL') |
+--------------------+------------------------+
| gfedcba | LQSYM习学 |
+--------------------+------------------------+
1 row in set (0.00 sec)
27、返回指定字符串位置的函数field
mysql> select field('ab', 'abc','cd','of','ab','ab'),field(null, 'abc','cd','of','ab',null);
+----------------------------------------+----------------------------------------+
| field('ab', 'abc','cd','of','ab','ab') | field(null, 'abc','cd','of','ab',null) |
+----------------------------------------+----------------------------------------+
| 4 | 0 |
+----------------------------------------+----------------------------------------+
1 row in set (0.00 sec)
28、返回指定日期对应的工作日索引dayofweek和weekday dayofweek从1周日开始 7为周六，weekday从0周一开始，6周日结束
mysql> select dayofweek('2019-1-30'),weekday('2019-1-30');
+------------------------+----------------------+
| dayofweek('2019-1-30') | weekday('2019-1-30') |
+------------------------+----------------------+
| 4 | 2 |
+------------------------+----------------------+
1 row in set (0.03 sec)
29、返回指定日期所在月中的日期索引dayofmonth
mysql> select dayofmonth('2017-4-4 0:18:20'),dayofmonth('2017-4-2'),dayofmonth('2017-2-30');
+--------------------------------+------------------------+-------------------------+
| dayofmonth('2017-4-4 0:18:20') | dayofmonth('2017-4-2') | dayofmonth('2017-2-30') |
+--------------------------------+------------------------+-------------------------+
| 4 | 2 | NULL |
+--------------------------------+------------------------+-------------------------+
1 row in set, 1 warning (0.00 sec)
30、返回指定日期所在年中的日期索引dayofyear
mysql> select dayofyear('2017-4-4 0:18:20'),dayofyear('2017-4-1'),dayofyear('2017-2-30');
+-------------------------------+-----------------------+------------------------+
| dayofyear('2017-4-4 0:18:20') | dayofyear('2017-4-1') | dayofyear('2017-2-30') |
+-------------------------------+-----------------------+------------------------+
| 94 | 91 | NULL |
+-------------------------------+-----------------------+------------------------+
1 row in set, 1 warning (0.00 sec)
31、返回指定日期对应的月份month
mysql> select month('2017-4-4 0:22:22'),month('2007-4-2'),month('2017-2-30');
+---------------------------+-------------------+--------------------+
| month('2017-4-4 0:22:22') | month('2007-4-2') | month('2017-2-30') |
+---------------------------+-------------------+--------------------+
| 4 | 4 | NULL |
+---------------------------+-------------------+--------------------+
1 row in set, 1 warning (0.00 sec)
32、返回指定日期对应的月名称monthname
mysql> select monthname('2017-4-4 0:22:22'),monthname('2007-4-2'),monthname('2017-2-30');
+-------------------------------+-----------------------+------------------------+
| monthname('2017-4-4 0:22:22') | monthname('2007-4-2') | monthname('2017-2-30') |
+-------------------------------+-----------------------+------------------------+
| April | April | NULL |
+-------------------------------+-----------------------+------------------------+
1 row in set, 1 warning (0.00 sec)
33、返回指定日期对应的工作日名称dayname
mysql> select dayname('2017-4-4 0:22:22'),dayname('2007-4-2'),dayname('2017-2-30');
+-----------------------------+---------------------+----------------------+
| dayname('2017-4-4 0:22:22') | dayname('2007-4-2') | dayname('2017-2-30') |
+-----------------------------+---------------------+----------------------+
| Tuesday | Monday | NULL |
+-----------------------------+---------------------+----------------------+
1 row in set, 1 warning (0.00 sec)
34、返回指定日期对应的季度quarter
mysql> select quarter('2017-4-4 0:22:22'),quarter('2007-4-2'),quarter('2017-2-30');
+-----------------------------+---------------------+----------------------+
| quarter('2017-4-4 0:22:22') | quarter('2007-4-2') | quarter('2017-2-30') |
+-----------------------------+---------------------+----------------------+
| 2 | 2 | NULL |
+-----------------------------+---------------------+----------------------+
1 row in set, 1 warning (0.00 sec)
35、返回指定日期对应的年份year
mysql> select year('2017-4-4 0:22:22'),year('2007-4-2'),year('2017-2-30');
+--------------------------+------------------+-------------------+
| year('2017-4-4 0:22:22') | year('2007-4-2') | year('2017-2-30') |
+--------------------------+------------------+-------------------+
| 2017 | 2007 | NULL |
+--------------------------+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
36、返回指定时间中的小时hour、分钟minute、秒数second
mysql> select hour('2017-4-4 0:28:12'),minute('2017-4-4 0:28:12'),second('2017-4-4 0:28:12');
+--------------------------+----------------------------+----------------------------+
| hour('2017-4-4 0:28:12') | minute('2017-4-4 0:28:12') | second('2017-4-4 0:28:12') |
+--------------------------+----------------------------+----------------------------+
| 0 | 28 | 12 |
+--------------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)
37、增加月份函数period_add
mysql> select period_add(1201,5),period_add(201101,12);
+--------------------+-----------------------+
| period_add(1201,5) | period_add(201101,12) |
+--------------------+-----------------------+
| 201206 | 201201 |
+--------------------+-----------------------+
1 row in set (0.00 sec)
38、取月份差函数period_diff
mysql> select period_diff(1201,1208),period_diff(201704,201104);
+------------------------+----------------------------+
| period_diff(1201,1208) | period_diff(201704,201104) |
+------------------------+----------------------------+
| -7 | 72 |
+------------------------+----------------------------+
1 row in set (0.00 sec)
39、返回当前日期函数curdate和current_date
mysql> select curdate(), current_date;
+------------+--------------+
| curdate() | current_date |
+------------+--------------+
| 2017-04-04 | 2017-04-04 |
+------------+--------------+
1 row in set (0.00 sec)
40、返回当前时间函数curtime和current_time
mysql> select curtime(), current_time;
+-----------+--------------+
| curtime() | current_time |
+-----------+--------------+
| 00:35:39 | 00:35:39 |
+-----------+--------------+
1 row in set (0.00 sec)
41、获取当前时间日期函数now和sysdate
mysql> select now(),sysdate(),sysdate()+0;
+---------------------+---------------------+----------------+
| now() | sysdate() | sysdate()+0 |
+---------------------+---------------------+----------------+
| 2017-04-04 00:36:37 | 2017-04-04 00:36:37 | 20170404003637 |
+---------------------+---------------------+----------------+
1 row in set (0.00 sec)
42、秒转换时间函数sec_to_time
mysql> select sec_to_time(60),sec_to_time(180),sec_to_time(8180);
+-----------------+------------------+-------------------+
| sec_to_time(60) | sec_to_time(180) | sec_to_time(8180) |
+-----------------+------------------+-------------------+
| 00:01:00 | 00:03:00 | 02:16:20 |
+-----------------+------------------+-------------------+
1 row in set (0.00 sec)
43、时间转换秒函数time_to_sec
mysql> select time_to_sec('00:01:23'),time_to_sec('1:20:21');
+-------------------------+------------------------+
| time_to_sec('00:01:23') | time_to_sec('1:20:21') |
+-------------------------+------------------------+
| 83 | 4821 |
+-------------------------+------------------------+
1 row in set (0.00 sec)
44、返回当前用户名函数session_user
mysql> select session_user();
+----------------+
| session_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
45、返回当前数据库名称函数database
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
46、返回字符串MD5校验和函数md5
mysql> select md5('md5test');
+----------------------------------+
| md5('md5test') |
+----------------------------------+
| 82da61aa724b5d149a9c5dc8682c2a45 |
+----------------------------------+
1 row in set (0.00 sec)
/*****************************************************************************/
/*************************视图************************************************/
功能类似于多表查询，可以将一个或多个表中的数据挑选出来独立成表
一、创建视图
1、单源表视图的创建
mysql> select * from studentInfo;
+----+--------+-------+-----------+-----------+
| id | name | score | subject | teacher |
+----+--------+-------+-----------+-----------+
| 1 | 小明 | 87.00 | 数学 | 王老师 |
| 2 | 小红 | 82.00 | 数学 | 王老师 |
| 3 | 小张 | 83.00 | 数学 | 王老师 |
| 4 | 小韩 | 88.00 | 数学 | 王老师 |
| 5 | 小胡 | 88.00 | 计算机 | 张老师 |
| 6 | 小胡 | 88.00 | 数据库 | 张老师 |
+----+--------+-------+-----------+-----------+
6 rows in set (0.00 sec)
mysql> create view hjf.view_score
-> AS(SELECT SI.id, SI.name, SI.score, SI.subject, SI.teacher
-> from studentInfo SI
-> where SI.score >= 85);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view_score;
+----+--------+-------+-----------+-----------+
| id | name | score | subject | teacher |
+----+--------+-------+-----------+-----------+
| 1 | 小明 | 87.00 | 数学 | 王老师 |
| 4 | 小韩 | 88.00 | 数学 | 王老师 |
| 5 | 小胡 | 88.00 | 计算机 | 张老师 |
| 6 | 小胡 | 88.00 | 数据库 | 张老师 |
+----+--------+-------+-----------+-----------+
4 rows in set (0.00 sec)
2、多源表视图的创建
mysql> create view hjf.view_test
-> AS(select newInfo.id, newInfo.name, newInfo.score, subjectInfo.subjectname, teacherInfo.teachername
-> from newInfo, subjectInfo, teacherInfo
-> where newInfo.subjectid = subjectInfo.id and newInfo.teacherid = teacherInfo.id);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view_test;
+----+--------+-------+-------------+-------------+
| id | name | score | subjectname | teachername |
+----+--------+-------+-------------+-------------+
| 1 | 小明 | 78.00 | 数学 | 张老师 |
| 2 | 小红 | 87.00 | 英语 | 王老师 |
| 3 | 小李 | 67.00 | 语文 | 吴老师 |
+----+--------+-------+-------------+-------------+
3 rows in set (0.02 sec)
二、修改视图
mysql> create or replace view hjf.view_test
-> AS(select newInfo.id, newInfo.name, newInfo.score, subjectInfo.subjectname
-> from newInfo, subjectInfo
-> where newInfo.subjectid = subjectInfo.id);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from view_test;
+----+--------+-------+-------------+
| id | name | score | subjectname |
+----+--------+-------+-------------+
| 3 | 小李 | 67.00 | 语文 |
| 1 | 小明 | 78.00 | 数学 |
| 2 | 小红 | 87.00 | 英语 |
+----+--------+-------+-------------+
3 rows in set (0.00 sec)
三、删除视图
mysql> drop view view_test;
Query OK, 0 rows affected (0.00 sec)
四、查看视图
1、查看视图的字段信息
mysql> describe(desc) view_test;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| score | decimal(4,2) | YES | | NULL | |
| subjectname | varchar(30) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2、查看创建视图的语句 \G 格式化显示结果
mysql> show create view view_test\G;
*************************** 1. row ***************************
View: view_test
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (select `newInfo`.`id` AS `id`,`newInfo`.`name` AS `name`,`newInfo`.`score` AS `score`,`subjectInfo`.`subjectname` AS `subjectname` from (`newInfo` join `subjectInfo`) where (`newInfo`.`subjectid` = `subjectInfo`.`id`))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
/*****************************************************************************/
/*************************索引************************************************/
一、管理索引
1、普通索引创建
mysql> create INDEX ind_name ON newInfo(name);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table newInfo add index ind_name(name);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
2、唯一索引创建
mysql> create unique index ind_subjectid on newInfo(subjectid);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table newInfo add index ind_subjectid(subjectid);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
3、主键索引创建
mysql> create table testInfo(id int, name varchar(20), primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> create table testInfo(id int, name varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> alter table testInfo add PRIMARY KEY(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
二、查看索引
mysql> show index from newInfo;
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| newInfo | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | |
| newInfo | 1 | ind_name | 1 | name | A | NULL | NULL | NULL | YES | BTREE | |
| newInfo | 1 | ind_subjectid | 1 | subjectid | A | NULL | NULL | NULL | YES | BTREE | |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
三、删除索引
mysql> drop index ind_name on newInfo;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table newInfo drop index ind_subjectid;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
/*****************************************************************************/
/*************************自定义函数******************************************/
1、创建自定义函数
mysql> delimiter $$
mysql> create function myfunc(a INT, b INT)
-> RETURNS INT
-> BEGIN
-> return a+b;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> select myfunc(2,3)$$
+-------------+
| myfunc(2,3) |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
2、删除函数
mysql> drop function myfunc;
Query OK, 0 rows affected (0.00 sec)
3、注释
创建函数的语句中含有分号，但我们并没有写完这条语句，mysql默认分号为止为一行语句，可以使用delimiter解决
mysql> delimiter $$//到$$为止是一条语句
恢复分号
mysql> delimiter ;//到;为止是一条语句
/*****************************************************************************/
/*************************存储过程******************************************/
1、创建存储过程
mysql> delimiter ##
mysql> create procedure my_prc() begin select * from newInfo; end##
Query OK, 0 rows affected (0.00 sec)
mysql> call my_prc##
+----+--------+-------+-----------+-----------+
| id | name | score | subjectid | teacherid |
+----+--------+-------+-----------+-----------+
| 1 | 小明 | 78.00 | 2 | 1 |
| 2 | 小红 | 87.00 | 3 | 2 |
| 3 | 小李 | 67.00 | 1 | 3 |
| 4 | 小胡 | 99.00 | 9 | 10 |
+----+--------+-------+-----------+-----------+
4 rows in set (0.00 sec)
2、修改存储过程
mysql> drop procedure if exists my_prc##
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure my_prc()
-> begin
-> select * from newInfo where id = 3;
-> end##
Query OK, 0 rows affected (0.00 sec)
3、删除存储过程
mysql> drop procedure if exists my_prc##
Query OK, 0 rows affected (0.00 sec)
4、存储过程中的变量使用
mysql> create procedure my_prc(a int, b int)
-> begin
-> declare c int;
-> set c = a + b;
-> select c as SUM;
-> end##
Query OK, 0 rows affected (0.00 sec)
mysql> call my_prc(2,4)##
+------+
| SUM |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
5、IF条件控制语句
mysql> create procedure my_prc(a int, b int)
-> begin
-> IF (a > b) THEN
-> select a as MAX;
-> ELSE
-> select b as MAX;
-> END IF;
-> end##
Query OK, 0 rows affected (0.00 sec)
mysql> call my_prc(2,4)
-> ##
+------+
| MAX |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
6、CASE条件控制语句
mysql> create procedure my_prc(a int, b int, ope varchar(1))
-> begin
-> CASE ope
-> WHEN '+' THEN select a+b as '和';
-> WHEN '-' THEN select a-b as '差';
-> WHEN '*' THEN select a*b as '积';
-> WHEN '/' THEN select a/b as '商';
-> END CASE;
-> end##
Query OK, 0 rows affected (0.00 sec)
mysql> call my_prc(2,4,'-')##
+------+
| 差 |
+------+
| -2 |
+------+
1 row in set (0.04 sec)
7、LOOP循环控制语句
mysql> create procedure my_prc(a int)
-> begin
-> label : LOOP
-> set a = a + 1;
-> IF (a < 10)
-> THEN select 'first label';
-> ELSE
-> ITERATE label;
-> END IF;
-> LEAVE label;
-> END LOOP label;
-> end##
Query OK, 0 rows affected (0.00 sec)
mysql> call my_prc(5)##
+-------------+
| first label |
+-------------+
| first label |
+-------------+
1 row in set (0.00 sec)
8、WHILE语句
mysql> create procedure my_prc()
-> begin
-> declare xx INT DEFAULT 10;
-> WHILE xx > 0 DO
-> set xx = xx - 1;
-> END WHILE;
-> set @vx = xx;
-> end##
Query OK, 0 rows affected (0.03 sec)
mysql> call my_prc()##
Query OK, 0 rows affected (0.00 sec)
mysql> select @vx##
+------+
| @vx |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
/*****************************************************************************/
/*************************触发器******************************************/
一、创建触发器
1、BEFORE INSERT型触发器
mysql> delimiter ##
mysql> create table hjf.logtab
-> (id int not null auto_increment primary key,
-> oname varchar(20),
-> otime varchar(30))##
Query OK, 0 rows affected (0.01 sec)
mysql> create trigger hjf.fstin
-> before insert ON newInfo
-> for each row
-> begin
-> insert into logtab(oname, otime) value('hjf', SYSDATE());
-> end##
Query OK, 0 rows affected (0.43 sec)
mysql> insert into hjf.newInfo values(12, 'Naic', 23.12, 2, 3)##
Query OK, 1 row affected (0.03 sec)
mysql> select * from hjf.logtab##
+----+-------+---------------------+
| id | oname | otime |
+----+-------+---------------------+
| 1 | hjf | 2017-04-08 20:18:01 |
+----+-------+---------------------+
1 row in set (0.00 sec)
mysql> select * from newInfo where id = 12##
+----+------+-------+-----------+-----------+
| id | name | score | subjectid | teacherid |
+----+------+-------+-----------+-----------+
| 12 | Naic | 23.12 | 2 | 3 |
+----+------+-------+-----------+-----------+
1 row in set (0.00 sec)
2、AFTER INSERT型触发器
mysql> create trigger hjf.secinsertrg
-> after insert ON newInfo
-> for each row
-> begin
-> insert into logtab(oname, otime) value('hjf_after', SYSDATE());
-> end##
Query OK, 0 rows affected (0.01 sec)
mysql> insert into hjf.newInfo values(14, 'Maic', 23.12, 2, 3)##
Query OK, 1 row affected (0.00 sec)
mysql> select * from logtab##
+----+-----------+---------------------+
| id | oname | otime |
+----+-----------+---------------------+
| 1 | hjf | 2017-04-08 20:18:01 |
| 2 | hjf | 2017-04-08 20:27:32 |
| 3 | hjf_after | 2017-04-08 20:27:32 |
+----+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from newInfo where id = 14##
+----+------+-------+-----------+-----------+
| id | name | score | subjectid | teacherid |
+----+------+-------+-----------+-----------+
| 14 | Maic | 23.12 | 2 | 3 |
+----+------+-------+-----------+-----------+
1 row in set (0.00 sec)
二、删除触发器
mysql> drop trigger hjf.secinsertrg##
Query OK, 0 rows affected (0.00 sec)
三、修改触发器
mysql> drop trigger hjf.secinsertrg##
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger hjf.secinsertrg
-> before delete ON newInfo
-> for each row
-> begin
-> insert into logtab(oname, otime) value('hjf_after', SYSDATE());
-> end##
Query OK, 0 rows affected (0.01 sec)
/*****************************************************************************/
select user(); ---查看当前用户；
create user abc identified by '123456';---创建用户，查看navicat
创建用户，权限
grant select,insert,update,delete on *.* to 用户名 @'%' identified by '123456‘；
grant select,insert,update,delete on *.* to abc @'%' identified by '123456‘
所有权限
grant all privileges on *.* to username@'%'
grant all privileges on *.* to abc@'%'
回收权限
revoke all privileges on *.* from username@‘%‘
revoke all privileges on *.* from abc@‘%‘
修改密码：
admin>mysqladmin -uroot -p123456 password 654321 将密码修改成654321
查看用户：
select * from mysql.user;
select user from mysql.user;
删除用户abc；
drop user abc;
修改密码
admin>mysqladmin -u用户名 -p旧密码 password 新密码
admin>mysqladmin -uroot -p123456 password 654321 -----密码修改成654321
查看有几个用户
select user from mysql.user;
/*****************************************************************************/
/*****************************数据库操作**************************************/
1、mysql -uroot -p //登录数据库
2、show databases; //显示所有数据库
3、create database database_name character set character_name;//创建数据库
create database database_name character set gbk;
4、alter database database_name character set utf8; //字符集改为utf8
set name utf8;
5、drop database database_name; //删除数据库
/*****************************************************************************/
/*****************************数据表操作**************************************/
6、create table productinfo (
id INT,
proname VARCHAR(20),
proprice FLOAT(5,2),
prodate DATETIME
); //建数据表
7、alter table table_name
ADD column_name | MODIFY column_name | DROP COLUMN column_name//修改表的操作
alter table productinfo ADD proquantity int;//添加列
alter table productinfo MODIFY proname VARCHAR(30);//修改列宽
alter table productinfo CHANGE proname pronamenew VARCHAR(30);//修改列名
alter table productinfo DROP COLUMN proquantity;//删除列
8、DROP TABLE table_name; //删除数据表
DROP TABLE productinfo;
9、desc productinfo； //显示表的结构
/*****************************************************************************/
/*****************************主键约束**************************************/
1、创建表时设置主键约束
(1)使用SQL语句设置单一列约束
a、设置列级主键约束
create table customerInfo(
id INT PRIMARY KEY,
name VARCHAR(20),
age INT
);
b、设置表级主键约束
create table customerInfo(
id INT,
name VARCHAR(20),
age INT,
PRIMARY KEY(id)
);
(2)在创建表时设置联合主键
a、设置列级主键约束
create table customerInfo(
id INT PRIMARY KEY,
name VARCHAR(20) PRIMARY KEY,
age INT
);
b、设置表级主键约束
create table customerInfo(
id INT,
name VARCHAR(20),
age INT,
PRIMARY KEY(id， name)
);
2、在修改表时添加主键约束
(1)在修改表时设置单一列约束
alter table table_name
ADD CONSTRAINT pk_name PRIMARY KEY;
alter table customerInfo ADD CONSTRAINT PK_customerInfo PRIMARY KEY(age);
(2)在修改表时设置联合主键
alter table table_name
ADD CONSTRAINT PK_name PRIMARY KEY();
alter table customerInfo ADD CONSTRAINT PK_customerInfo PRIMARY KEY(id, age);
3、删除主键
alter table table_name DROP PRIMARY KEY;
/*****************************************************************************/
/*****************************外键约束**************************************/
1、创建表时设置外键约束
create table TypeInfo(roomtypeId INT PRIMARY KEY, roomtype VARCHAR(20));
create table RoomInfo(
roomId INT PRIMARY KEY,
roomtypeId INT,
roomname VARCHAR(30),
CONSTRAINT FK_RoomInfo FOREIGN KEY(roomId) REFERENCES TypeInfo(roomtypeId)
);
2、修改表时添加外键约束
alter table table_name1 ADD CONSTRAINT fk_name FOREIGN KEY(列1)REFERENCES table_name2(列2);
alter table RoomInfo ADD CONSTRAINT FK_RoomInfo FOREIGN KEY(roomtypeId) REFERENCES TypeInfo(roomtypeId);
3、删除外键
alter table table_name DROP FOREIGN KEY FK_NAME;
alter table RoomInfo DROP FOREIGN KEY FK_RoomInfo;
/*****************************************************************************/
/*****************************默认值约束**************************************/
1、创建表时设置默认值约束
create table table_name(id int DEFAULT 0, name VARCHAR(20));//(0为默认值)
2、修改表时添加默认值约束
alter table table_name alter id set 2;
3、删除默认值
alter table table_name alter id DROP DEFAULT;
/*****************************************************************************/
/*****************************非空约束**************************************/
1、创建表时设置非空约束
create table table_name(id int NOT NULL, name VARCHAR(20));
2、修改表时添加非空约束
alter table table_name MODIFY name VARCHAR(20) NOT NULL;
/*****************************************************************************/
/*****************************检查约束**************************************/
1、创建表时设置检查约束
create table table_name(age int CHECK(age >= 18), name VARCHAR(20));
2、修改表时添加检查约束
alter table table_name ADD CONSTRAINT UQ_age CHECK(age >= 20);
注意：mysql中虽然可以设置检查约束，但是检查约束在表中是不生效的，仍然可以插入不符合条件的数据。
/*****************************************************************************/
/*****************************唯一约束**************************************/
1、创建表时设置唯一约束
create table table_name(age int, name VARCHAR(20) UNIQUE);
create table table_name(age int, name VARCHAR(20), UNIQUE(name));
共同唯一约束
create table table_name(age INT, name VARCHAR(20), UNIQUE(age, name));
2、修改表时添加唯一约束
alter table table_name ADD CONSTRAINT UQ_name UNIQUE(name);
3、删除唯一约束
DROP INDEX UQ_name ON table_name;
/*****************************************************************************/
/*****************************DML语言操作数据表**************************************/
一、insert语句
1、insert value形式
insert into table_name value(1, '小明');
2、insert set形式
insert into table_name set id = 2, name = '小红';
3、insert select形式——复制其他表数据
create table table_name1(id int, name VARCHAR(30));
insert into table_name1 value(10, '老王');
insert into table_name select * from table_name1;
4、添加多条数据
insert into table_name value(3, '小张'), (4, '小李');
二、update语句
1、根据条件修改表中的数据
update table_name set name = '小韩' where id = 1;
2、根据顺序修改表中的数据
update table_name set ORDER BY id desc/asc;(降序/升序)
3、限制行数的修改
update table_name set name = 'Naic' where id >= 10 LIMIT 2;
四、delete语句
1、删除表中所有数据
detele from table_name;
truncate table table_name;
2、根据条件删除数据
delete from table_name where id = 1;
3、按指定顺序删除数据
delete from table_name order by id desc;
4、限制行数的删除
delete from table_name order by id desc LIMIT 1;
/*****************************************************************************/
/*****************************简单查询**************************************/
1、查询表中的全部数据
select * from table_name;
2、查询指定字段的数据
select id, name from table_name;
3、在查询中使用别名列
select id as '编号', name as '姓名' from table_name;
4、根据单一条件查询数据
select * from table_name where id = 1;
5、带like条件的查询
select * from table_name where name like '%老%';
6、根据多个条件查询数据
select * from table_name where id = 1 and(or) name = '小明';
7、对查询结果进行排序
select * from table_name ORDER BY id desc(asc);
8、限制查询结果行数
select * from table_name LIMIT 2;
/*****************************************************************************/
/*****************************聚合函数**************************************/
1、最大值函数MAX
select MAX(id) from table_name;
2、最小值函数MIN
select MIN(id) from table_name;
3、平均值函数AVG
select AVG(id) from table_name;
4、求和函数SUM
select SUM(id) as '总和' from table_name;
5、计数函数COUNT COUNT只计算非NULL值，COUNT(*)都计算
select COUNT(id) as '人数' from table_name;
/*****************************************************************************/
/*****************************子查询**************************************/
1、使用IN的子查询 name包含于(select name from table_name1)
select * from table_name where name IN(select name from table_name1);
2、使用EXIST的子查询 where true(false)true打印 false无结果
select * from table_name where EXIST (select * from table_name1 where name = 'mike');
3、使用ANY的子查询 name 包含于ANY(select name from table_name1) 返回true
select * from table_name where name = ANY(select name from table_name1);
/*****************************************************************************/
/*****************************分组查询**************************************/
create table studentInfo(id int PRIMARY KEY, name VARCHAR(20), score decimal(4,2), subject VARCHAR(20), teacher VARCHAR(20));
1、对单列进行分组查询
select subject, COUNT(*) from studentInfo GROUP BY subject;
2、使用HAVING的分组查询
select subject, AVG(score) from studentInfo GROUP BY subject HAVING subject = '数学';
3、对多列进行分组查询
select subject, teacher, AVG(score) from studentInfo GROUP BY subject, teacher;
4、在分组查询中使用ORDER BY
select teacher, SUM(score) from studentInfo GROUP BY teacher ORDER BY sum(score) desc;
/*****************************************************************************/
/*****************************多表查询**************************************/
create table newInfo(id int PRIMARY KEY, name VARCHAR(20), score decimal(4,2), subjectid int, teacherid int);
create table subjectInfo(id int PRIMARY KEY, subjectname VARCHAR(30));
create table teacherInfo(id int PRIMARY KEY, teachername VARCHAR(30));
1、等值连接
mysql> select newInfo.name,subjectInfo.subjectname from newInfo,subjectInfo where newInfo.subjectid=subjectInfo.id;
+--------+-------------+
| name | subjectname |
+--------+-------------+
| 小明 | 数学 |
| 小红 | 英语 |
| 小李 | 语文 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select newInfo.name,subjectInfo.subjectname,teacherInfo.teachername from newInfo,subjectInfo,teacherInfo where newInfo.subjectid = subjectInfo.id and newInfo.teacherid = teacherInfo.id;
+--------+-------------+-------------+
| name | subjectname | teachername |
+--------+-------------+-------------+
| 小明 | 数学 | 张老师 |
| 小红 | 英语 | 王老师 |
| 小李 | 语文 | 吴老师 |
+--------+-------------+-------------+
3 rows in set (0.00 sec)
2、笛卡尔积 3*4列和5*7列表不加条件查询，得到8*28列表
select * from newInfo,subjectInfo,teacherInfo;
查询所得结果非常庞大，并且非常臃肿，对实际的应用没有什么太大的意义，在实际应用中要加指定查询条件，避免笛卡尔积的出现
3、外连接
(1)左外连接
mysql> select newInfo.name,subjectInfo.subjectname from newInfo LEFT OUTER JOIN subjectInfo ON newInfo.subjectid = subjectInfo.id;
+--------+-------------+
| name | subjectname |
+--------+-------------+
| 小明 | 数学 |
| 小红 | 英语 |
| 小李 | 语文 |
| 小胡 | NULL |
+--------+-------------+
4 rows in set (0.00 sec)
(2)右外连接
mysql> select newInfo.name,subjectInfo.subjectname from newInfo RIGHT OUTER JOIN subjectInfo ON newInfo.subjectid = subjectInfo.id;
+--------+-------------+
| name | subjectname |
+--------+-------------+
| 小李 | 语文 |
| 小明 | 数学 |
| 小红 | 英语 |
| NULL | 计算机 |
+--------+-------------+
4 rows in set (0.00 sec)
4、内连接 相当于等值连接 相比较更加明显是多表查询
mysql> select newInfo.name,subjectInfo.subjectname from newInfo INNER JOIN subjectInfo ON newInfo.subjectid = subjectInfo.id;
+--------+-------------+
| name | subjectname |
+--------+-------------+
| 小李 | 语文 |
| 小明 | 数学 |
| 小红 | 英语 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select newInfo.name,subjectInfo.subjectname,teacherInfo.teachername from newInfo INNER JOIN subjectInfo INNER JOIN teacherInfo ON newInfo.subjectid=subjectInfo.id and newInfo.teacherid=teacherInfo.id;
+--------+-------------+-------------+
| name | subjectname | teachername |
+--------+-------------+-------------+
| 小明 | 数学 | 张老师 |
| 小红 | 英语 | 王老师 |
| 小李 | 语文 | 吴老师 |
+--------+-------------+-------------+
3 rows in set (0.00 sec)
/*****************************************************************************/
/*****************************合并查询结果**************************************/
1、使用UNION关键字合并查询结果（多表查询的数据表） 两张表的列数相同，数据类型相同
mysql> select * from teacherInfo
-> UNION
-> select * from subjectInfo;
+----+-------------+
| id | teachername |
+----+-------------+
| 1 | 张老师 |
| 2 | 王老师 |
| 3 | 吴老师 |
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 计算机 |
+----+-------------+
7 rows in set (0.00 sec)
2、对合并后的查询结果排序
mysql> select * from teacherInfo
-> UNION
-> select * from subjectInfo
-> ORDER BY id;
+----+-------------+
| id | teachername |
+----+-------------+
| 1 | 张老师 |
| 1 | 语文 |
| 2 | 数学 |
| 2 | 王老师 |
| 3 | 英语 |
| 3 | 吴老师 |
| 4 | 计算机 |
+----+-------------+
7 rows in set (0.00 sec)
3、限制组合查询结果的行数
mysql> select * from teacherInfo
-> UNION
-> select * from subjectInfo
-> LIMIT 3;
+----+-------------+
| id | teachername |
+----+-------------+
| 1 | 张老师 |
| 2 | 王老师 |
| 3 | 吴老师 |
+----+-------------+
3 rows in set (0.00 sec)
/*****************************************************************************/
/*****************************函数**************************************/
1、绝对值函数abs
mysql> select abs(-21), abs(21), abs('-1'), abs(-91/4);
+----------+---------+-----------+------------+
| abs(-21) | abs(21) | abs('-1') | abs(-91/4) |
+----------+---------+-----------+------------+
| 21 | 21 | 1 | 22.7500 |
+----------+---------+-----------+------------+
1 row in set (0.02 sec)
2、取余函数mod
mysql> select mod(10, 3), mod('10', 3), mod(10,5/3), mod(-10, 2), mod(1, 0);
+------------+--------------+-------------+-------------+-----------+
| mod(10, 3) | mod('10', 3) | mod(10,5/3) | mod(-10, 2) | mod(1, 0) |
+------------+--------------+-------------+-------------+-----------+
| 1 | 1 | 0.0000 | 0 | NULL |
+------------+--------------+-------------+-------------+-----------+
1 row in set (0.00 sec)
3、求平方根函数sqrt
mysql> select sqrt(-100), sqrt(100), sqrt(100.89), sqrt('100.89');
+------------+-----------+------------------+------------------+
| sqrt(-100) | sqrt(100) | sqrt(100.89) | sqrt('100.89') |
+------------+-----------+------------------+------------------+
| NULL | 10 | 10.0444014256699 | 10.0444014256699 |
+------------+-----------+------------------+------------------+
1 row in set (0.00 sec)
4、获取随机数函数rand 可返回一个随机浮点值 0~1.0 带参数作为种子值
mysql> select rand(), rand(2), rand(5.1);
+-------------------+-------------------+-------------------+
| rand() | rand(2) | rand(5.1) |
+-------------------+-------------------+-------------------+
| 0.967204678773139 | 0.655586646549019 | 0.406135974830143 |
+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)
5、四舍五入函数round
mysql> select round(6.54321, 2), round(6.54321, 2.88), round(6.54321, -1);
+-------------------+----------------------+--------------------+
| round(6.54321, 2) | round(6.54321, 2.88) | round(6.54321, -1) |
+-------------------+----------------------+--------------------+
| 6.54 | 6.543 | 10 |
+-------------------+----------------------+--------------------+
1 row in set (0.00 sec)
6、符号函数sign 正数返1,0返0，负数返-1
mysql> select sign(21), sign('321'), sign(-1), sign(0.00),sign(-1*'1');
+----------+-------------+----------+------------+--------------+
| sign(21) | sign('321') | sign(-1) | sign(0.00) | sign(-1*'1') |
+----------+-------------+----------+------------+--------------+
| 1 | 1 | -1 | 0 | -1 |
+----------+-------------+----------+------------+--------------+
1 row in set (0.00 sec)
7、幂运算函数power
mysql> select power(8,2), power('8', 3), power(2.1, 2.5), power(-2, 2), power(-1,2.2);
+------------+---------------+------------------+--------------+---------------+
| power(8,2) | power('8', 3) | power(2.1, 2.5) | power(-2, 2) | power(-1,2.2) |
+------------+---------------+------------------+--------------+---------------+
| 64 | 512 | 6.39069714506954 | 4 | NULL |
+------------+---------------+------------------+--------------+---------------+
1 row in set (0.00 sec)
8、对数运算函数log 无参代表ln()
mysql> select log(5, 25), log(3, '9'), log(2), log(-2);
+------------+-------------+-------------------+---------+
| log(5, 25) | log(3, '9') | log(2) | log(-2) |
+------------+-------------+-------------------+---------+
| 2 | 2 | 0.693147180559945 | NULL |
+------------+-------------+-------------------+---------+
1 row in set (0.00 sec)
9、pi函数
mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
10、三角函数
mysql> select sin(pi()/2), cos(pi()), tan(pi()/4);
+-------------+-----------+-------------+
| sin(pi()/2) | cos(pi()) | tan(pi()/4) |
+-------------+-----------+-------------+
| 1 | -1 | 1 |
+-------------+-----------+-------------+
1 row in set (0.00 sec)
11、获取最小整数ceil、ceiling
mysql> select ceiling(-234.32), ceil(-234.32), ceiling(234.32), ceil(234.32);
+------------------+---------------+-----------------+--------------+
| ceiling(-234.32) | ceil(-234.32) | ceiling(234.32) | ceil(234.32) |
+------------------+---------------+-----------------+--------------+
| -234 | -234 | 235 | 235 |
+------------------+---------------+-----------------+--------------+
1 row in set (0.00 sec)
12、合并字符串函数concat
mysql> select concat('this','a','test'),concat('this', 'a', 'test',null),concat(123, 456);
+---------------------------+----------------------------------+------------------+
| concat('this','a','test') | concat('this', 'a', 'test',null) | concat(123, 456) |
+---------------------------+----------------------------------+------------------+
| thisatest | NULL | 123456 |
+---------------------------+----------------------------------+------------------+
1 row in set (0.00 sec)
13、计算字符串长度函数length char_length()以字符为单位，多字节字符当成一个字符，length()以字节为单位
mysql> select char_length('1234'),char_length('测试'), length('测试');
+---------------------+-----------------------+------------------+
| char_length('1234') | char_length('测试') | length('测试') |
+---------------------+-----------------------+------------------+
| 4 | 2 | 6 |
+---------------------+-----------------------+------------------+
1 row in set (0.00 sec)
14、字母小写转大写upper
mysql> select upper('abcd测试'), ucase('abcd测试');
+---------------------+---------------------+
| upper('abcd测试') | ucase('abcd测试') |
+---------------------+---------------------+
| ABCD测试 | ABCD测试 |
+---------------------+---------------------+
1 row in set (0.00 sec)
15、字母大写转小写lower
mysql> select lower('ABCD测试'), lcase('ABCD测试');
+---------------------+---------------------+
| lower('ABCD测试') | lcase('ABCD测试') |
+---------------------+---------------------+
| abcd测试 | abcd测试 |
+---------------------+---------------------+
1 row in set (0.00 sec)
16、获取指定长度的字符串的函数left和right
mysql> select left('left测试函数', 5), right('right测试函数', 2);
+-----------------------------+-------------------------------+
| left('left测试函数', 5) | right('right测试函数', 2) |
+-----------------------------+-------------------------------+
| left测 | 函数 |
+-----------------------------+-------------------------------+
1 row in set (0.00 sec)
17、填充字符串函数lpad和rpad
mysql> select lpad('测试函数', 8, 'lpad'),rpad('测试函数',8,'rpad'),rpad('测试函数',2,'MYSQL');
+---------------------------------+-------------------------------+--------------------------------+
| lpad('测试函数', 8, 'lpad') | rpad('测试函数',8,'rpad') | rpad('测试函数',2,'MYSQL') |
+---------------------------------+-------------------------------+--------------------------------+
| lpad测试函数 | 测试函数rpad | 测试 |
+---------------------------------+-------------------------------+--------------------------------+
1 row in set (0.00 sec)
18、删除指定字符函数trim both两端删除 leading前端删除 trailing后端删除 无参删两端空格
mysql> select trim(trailing 'm' from 'msqltrim'), trim(both 'm' from 'mysqltrim'), trim(' mysqltrim ');
+------------------------------------+---------------------------------+---------------------+
| trim(trailing 'm' from 'msqltrim') | trim(both 'm' from 'mysqltrim') | trim(' mysqltrim ') |
+------------------------------------+---------------------------------+---------------------+
| msqltri | ysqltri | mysqltrim |
+------------------------------------+---------------------------------+---------------------+
19、删除两端空格函数ltrim，rtrim
mysql> select length(' mysql'), length('mysql '), length(ltrim(' mysql')), length(rtrim('mysql '));
+-------------------+-------------------+--------------------------+--------------------------+
| length(' mysql') | length('mysql ') | length(ltrim(' mysql')) | length(rtrim('mysql ')) |
+-------------------+-------------------+--------------------------+--------------------------+
| 7 | 7 | 5 | 5 |
+-------------------+-------------------+--------------------------+--------------------------+
1 row in set (0.00 sec)
20、重复生成字符串的函数repeat
mysql> select repeat('测试',3),repeat('测试',-1), repeat('测试',null);
+--------------------+---------------------+-----------------------+
| repeat('测试',3) | repeat('测试',-1) | repeat('测试',null) |
+--------------------+---------------------+-----------------------+
| 测试测试测试 | | NULL |
+--------------------+---------------------+-----------------------+
1 row in set (0.00 sec)
21、空格函数space
mysql> select length(space(10));
+-------------------+
| length(space(10)) |
+-------------------+
| 10 |
+-------------------+
1 row in set (0.00 sec)
22、替换函数replace
mysql> select replace('this is test', 't', 'T');
+-----------------------------------+
| replace('this is test', 't', 'T') |
+-----------------------------------+
| This is TesT |
+-----------------------------------+
1 row in set (0.00 sec)
23、替换字符串的函数insert
mysql> select insert('testinsert',2,2,'uu'),insert('testinsert',2,5,'uu'),insert('testinsert',2,1,'uuu');
+-------------------------------+-------------------------------+--------------------------------+
| insert('testinsert',2,2,'uu') | insert('testinsert',2,5,'uu') | insert('testinsert',2,1,'uuu') |
+-------------------------------+-------------------------------+--------------------------------+
| tuutinsert | tuusert | tuuustinsert |
+-------------------------------+-------------------------------+--------------------------------+
1 row in set (0.00 sec)
24、比较字符串大小的函数strcmp
mysql> select strcmp('strcmptest','strcmptest'),strcmp('stacmp','strcmp'),strcmp('strcmp','stacmp');
+-----------------------------------+---------------------------+---------------------------+
| strcmp('strcmptest','strcmptest') | strcmp('stacmp','strcmp') | strcmp('strcmp','stacmp') |
+-----------------------------------+---------------------------+---------------------------+
| 0 | -1 | 1 |
+-----------------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)
25、获取子串函数substring
mysql> select substring('substringtest',4,3),substring('substringtest',4);
+--------------------------------+------------------------------+
| substring('substringtest',4,3) | substring('substringtest',4) |
+--------------------------------+------------------------------+
| str | stringtest |
+--------------------------------+------------------------------+
1 row in set (0.00 sec)
26、字符串逆序函数reverse
mysql> select reverse('abcdefg'),reverse('学习MYSQL');
+--------------------+------------------------+
| reverse('abcdefg') | reverse('学习MYSQL') |
+--------------------+------------------------+
| gfedcba | LQSYM习学 |
+--------------------+------------------------+
1 row in set (0.00 sec)
27、返回指定字符串位置的函数field
mysql> select field('ab', 'abc','cd','of','ab','ab'),field(null, 'abc','cd','of','ab',null);
+----------------------------------------+----------------------------------------+
| field('ab', 'abc','cd','of','ab','ab') | field(null, 'abc','cd','of','ab',null) |
+----------------------------------------+----------------------------------------+
| 4 | 0 |
+----------------------------------------+----------------------------------------+
1 row in set (0.00 sec)
28、返回指定日期对应的工作日索引dayofweek和weekday dayofweek从1周日开始 7为周六，weekday从0周一开始，6周日结束
mysql> select dayofweek('2019-1-30'),weekday('2019-1-30');
+------------------------+----------------------+
| dayofweek('2019-1-30') | weekday('2019-1-30') |
+------------------------+----------------------+
| 4 | 2 |
+------------------------+----------------------+
1 row in set (0.03 sec)
29、返回指定日期所在月中的日期索引dayofmonth
mysql> select dayofmonth('2017-4-4 0:18:20'),dayofmonth('2017-4-2'),dayofmonth('2017-2-30');
+--------------------------------+------------------------+-------------------------+
| dayofmonth('2017-4-4 0:18:20') | dayofmonth('2017-4-2') | dayofmonth('2017-2-30') |
+--------------------------------+------------------------+-------------------------+
| 4 | 2 | NULL |
+--------------------------------+------------------------+-------------------------+
1 row in set, 1 warning (0.00 sec)
30、返回指定日期所在年中的日期索引dayofyear
mysql> select dayofyear('2017-4-4 0:18:20'),dayofyear('2017-4-1'),dayofyear('2017-2-30');
+-------------------------------+-----------------------+------------------------+
| dayofyear('2017-4-4 0:18:20') | dayofyear('2017-4-1') | dayofyear('2017-2-30') |
+-------------------------------+-----------------------+------------------------+
| 94 | 91 | NULL |
+-------------------------------+-----------------------+------------------------+
1 row in set, 1 warning (0.00 sec)
31、返回指定日期对应的月份month
mysql> select month('2017-4-4 0:22:22'),month('2007-4-2'),month('2017-2-30');
+---------------------------+-------------------+--------------------+
| month('2017-4-4 0:22:22') | month('2007-4-2') | month('2017-2-30') |
+---------------------------+-------------------+--------------------+
| 4 | 4 | NULL |
+---------------------------+-------------------+--------------------+
1 row in set, 1 warning (0.00 sec)
32、返回指定日期对应的月名称monthname
mysql> select monthname('2017-4-4 0:22:22'),monthname('2007-4-2'),monthname('2017-2-30');
+-------------------------------+-----------------------+------------------------+
| monthname('2017-4-4 0:22:22') | monthname('2007-4-2') | monthname('2017-2-30') |
+-------------------------------+-----------------------+------------------------+
| April | April | NULL |
+-------------------------------+-----------------------+------------------------+
1 row in set, 1 warning (0.00 sec)
33、返回指定日期对应的工作日名称dayname
mysql> select dayname('2017-4-4 0:22:22'),dayname('2007-4-2'),dayname('2017-2-30');
+-----------------------------+---------------------+----------------------+
| dayname('2017-4-4 0:22:22') | dayname('2007-4-2') | dayname('2017-2-30') |
+-----------------------------+---------------------+----------------------+
| Tuesday | Monday | NULL |
+-----------------------------+---------------------+----------------------+
1 row in set, 1 warning (0.00 sec)
34、返回指定日期对应的季度quarter
mysql> select quarter('2017-4-4 0:22:22'),quarter('2007-4-2'),quarter('2017-2-30');
+-----------------------------+---------------------+----------------------+
| quarter('2017-4-4 0:22:22') | quarter('2007-4-2') | quarter('2017-2-30') |
+-----------------------------+---------------------+----------------------+
| 2 | 2 | NULL |
+-----------------------------+---------------------+----------------------+
1 row in set, 1 warning (0.00 sec)
35、返回指定日期对应的年份year
mysql> select year('2017-4-4 0:22:22'),year('2007-4-2'),year('2017-2-30');
+--------------------------+------------------+-------------------+
| year('2017-4-4 0:22:22') | year('2007-4-2') | year('2017-2-30') |
+--------------------------+------------------+-------------------+
| 2017 | 2007 | NULL |
+--------------------------+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
36、返回指定时间中的小时hour、分钟minute、秒数second
mysql> select hour('2017-4-4 0:28:12'),minute('2017-4-4 0:28:12'),second('2017-4-4 0:28:12');
+--------------------------+----------------------------+----------------------------+
| hour('2017-4-4 0:28:12') | minute('2017-4-4 0:28:12') | second('2017-4-4 0:28:12') |
+--------------------------+----------------------------+----------------------------+
| 0 | 28 | 12 |
+--------------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)
37、增加月份函数period_add
mysql> select period_add(1201,5),period_add(201101,12);
+--------------------+-----------------------+
| period_add(1201,5) | period_add(201101,12) |
+--------------------+-----------------------+
| 201206 | 201201 |
+--------------------+-----------------------+
1 row in set (0.00 sec)
38、取月份差函数period_diff
mysql> select period_diff(1201,1208),period_diff(201704,201104);
+------------------------+----------------------------+
| period_diff(1201,1208) | period_diff(201704,201104) |
+------------------------+----------------------------+
| -7 | 72 |
+------------------------+----------------------------+
1 row in set (0.00 sec)
39、返回当前日期函数curdate和current_date
mysql> select curdate(), current_date;
+------------+--------------+
| curdate() | current_date |
+------------+--------------+
| 2017-04-04 | 2017-04-04 |
+------------+--------------+
1 row in set (0.00 sec)
40、返回当前时间函数curtime和current_time
mysql> select curtime(), current_time;
+-----------+--------------+
| curtime() | current_time |
+-----------+--------------+
| 00:35:39 | 00:35:39 |
+-----------+--------------+
1 row in set (0.00 sec)
41、获取当前时间日期函数now和sysdate
mysql> select now(),sysdate(),sysdate()+0;
+---------------------+---------------------+----------------+
| now() | sysdate() | sysdate()+0 |
+---------------------+---------------------+----------------+
| 2017-04-04 00:36:37 | 2017-04-04 00:36:37 | 20170404003637 |
+---------------------+---------------------+----------------+
1 row in set (0.00 sec)
42、秒转换时间函数sec_to_time
mysql> select sec_to_time(60),sec_to_time(180),sec_to_time(8180);
+-----------------+------------------+-------------------+
| sec_to_time(60) | sec_to_time(180) | sec_to_time(8180) |
+-----------------+------------------+-------------------+
| 00:01:00 | 00:03:00 | 02:16:20 |
+-----------------+------------------+-------------------+
1 row in set (0.00 sec)
43、时间转换秒函数time_to_sec
mysql> select time_to_sec('00:01:23'),time_to_sec('1:20:21');
+-------------------------+------------------------+
| time_to_sec('00:01:23') | time_to_sec('1:20:21') |
+-------------------------+------------------------+
| 83 | 4821 |
+-------------------------+------------------------+
1 row in set (0.00 sec)
44、返回当前用户名函数session_user
mysql> select session_user();
+----------------+
| session_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
45、返回当前数据库名称函数database
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
46、返回字符串MD5校验和函数md5
mysql> select md5('md5test');
+----------------------------------+
| md5('md5test') |
+----------------------------------+
| 82da61aa724b5d149a9c5dc8682c2a45 |
+----------------------------------+
1 row in set (0.00 sec)
/*****************************************************************************/
/*************************视图************************************************/
功能类似于多表查询，可以将一个或多个表中的数据挑选出来独立成表
一、创建视图
1、单源表视图的创建
mysql> select * from studentInfo;
+----+--------+-------+-----------+-----------+
| id | name | score | subject | teacher |
+----+--------+-------+-----------+-----------+
| 1 | 小明 | 87.00 | 数学 | 王老师 |
| 2 | 小红 | 82.00 | 数学 | 王老师 |
| 3 | 小张 | 83.00 | 数学 | 王老师 |
| 4 | 小韩 | 88.00 | 数学 | 王老师 |
| 5 | 小胡 | 88.00 | 计算机 | 张老师 |
| 6 | 小胡 | 88.00 | 数据库 | 张老师 |
+----+--------+-------+-----------+-----------+
6 rows in set (0.00 sec)
mysql> create view hjf.view_score
-> AS(SELECT SI.id, SI.name, SI.score, SI.subject, SI.teacher
-> from studentInfo SI
-> where SI.score >= 85);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view_score;
+----+--------+-------+-----------+-----------+
| id | name | score | subject | teacher |
+----+--------+-------+-----------+-----------+
| 1 | 小明 | 87.00 | 数学 | 王老师 |
| 4 | 小韩 | 88.00 | 数学 | 王老师 |
| 5 | 小胡 | 88.00 | 计算机 | 张老师 |
| 6 | 小胡 | 88.00 | 数据库 | 张老师 |
+----+--------+-------+-----------+-----------+
4 rows in set (0.00 sec)
2、多源表视图的创建
mysql> create view hjf.view_test
-> AS(select newInfo.id, newInfo.name, newInfo.score, subjectInfo.subjectname, teacherInfo.teachername
-> from newInfo, subjectInfo, teacherInfo
-> where newInfo.subjectid = subjectInfo.id and newInfo.teacherid = teacherInfo.id);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view_test;
+----+--------+-------+-------------+-------------+
| id | name | score | subjectname | teachername |
+----+--------+-------+-------------+-------------+
| 1 | 小明 | 78.00 | 数学 | 张老师 |
| 2 | 小红 | 87.00 | 英语 | 王老师 |
| 3 | 小李 | 67.00 | 语文 | 吴老师 |
+----+--------+-------+-------------+-------------+
3 rows in set (0.02 sec)
二、修改视图
mysql> create or replace view hjf.view_test
-> AS(select newInfo.id, newInfo.name, newInfo.score, subjectInfo.subjectname
-> from newInfo, subjectInfo
-> where newInfo.subjectid = subjectInfo.id);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from view_test;
+----+--------+-------+-------------+
| id | name | score | subjectname |
+----+--------+-------+-------------+
| 3 | 小李 | 67.00 | 语文 |
| 1 | 小明 | 78.00 | 数学 |
| 2 | 小红 | 87.00 | 英语 |
+----+--------+-------+-------------+
3 rows in set (0.00 sec)
三、删除视图
mysql> drop view view_test;
Query OK, 0 rows affected (0.00 sec)
四、查看视图
1、查看视图的字段信息
mysql> describe(desc) view_test;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| score | decimal(4,2) | YES | | NULL | |
| subjectname | varchar(30) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2、查看创建视图的语句 \G 格式化显示结果
mysql> show create view view_test\G;
*************************** 1. row ***************************
View: view_test
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (select `newInfo`.`id` AS `id`,`newInfo`.`name` AS `name`,`newInfo`.`score` AS `score`,`subjectInfo`.`subjectname` AS `subjectname` from (`newInfo` join `subjectInfo`) where (`newInfo`.`subjectid` = `subjectInfo`.`id`))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
/*****************************************************************************/
/*************************索引************************************************/
一、管理索引
1、普通索引创建
mysql> create INDEX ind_name ON newInfo(name);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table newInfo add index ind_name(name);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
2、唯一索引创建
mysql> create unique index ind_subjectid on newInfo(subjectid);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table newInfo add index ind_subjectid(subjectid);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
3、主键索引创建
mysql> create table testInfo(id int, name varchar(20), primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> create table testInfo(id int, name varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> alter table testInfo add PRIMARY KEY(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
二、查看索引
mysql> show index from newInfo;
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| newInfo | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | |
| newInfo | 1 | ind_name | 1 | name | A | NULL | NULL | NULL | YES | BTREE | |
| newInfo | 1 | ind_subjectid | 1 | subjectid | A | NULL | NULL | NULL | YES | BTREE | |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
三、删除索引
mysql> drop index ind_name on newInfo;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table newInfo drop index ind_subjectid;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
/*****************************************************************************/
/*************************自定义函数******************************************/
1、创建自定义函数
mysql> delimiter $$
mysql> create function myfunc(a INT, b INT)
-> RETURNS INT
-> BEGIN
-> return a+b;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> select myfunc(2,3)$$
+-------------+
| myfunc(2,3) |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
2、删除函数
mysql> drop function myfunc;
Query OK, 0 rows affected (0.00 sec)
3、注释
创建函数的语句中含有分号，但我们并没有写完这条语句，mysql默认分号为止为一行语句，可以使用delimiter解决
mysql> delimiter $$//到$$为止是一条语句
恢复分号
mysql> delimiter ;//到;为止是一条语句
/*****************************************************************************/
/*************************存储过程******************************************/
1、创建存储过程
mysql> delimiter ##
mysql> create procedure my_prc() begin select * from newInfo; end##
Query OK, 0 rows affected (0.00 sec)
mysql> call my_prc##
+----+--------+-------+-----------+-----------+
| id | name | score | subjectid | teacherid |
+----+--------+-------+-----------+-----------+
| 1 | 小明 | 78.00 | 2 | 1 |
| 2 | 小红 | 87.00 | 3 | 2 |
| 3 | 小李 | 67.00 | 1 | 3 |
| 4 | 小胡 | 99.00 | 9 | 10 |
+----+--------+-------+-----------+-----------+
4 rows in set (0.00 sec)
2、修改存储过程
mysql> drop procedure if exists my_prc##
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure my_prc()
-> begin
-> select * from newInfo where id = 3;
-> end##
Query OK, 0 rows affected (0.00 sec)
3、删除存储过程
mysql> drop procedure if exists my_prc##
Query OK, 0 rows affected (0.00 sec)
4、存储过程中的变量使用
mysql> create procedure my_prc(a int, b int)
-> begin
-> declare c int;
-> set c = a + b;
-> select c as SUM;
-> end##
Query OK, 0 rows affected (0.00 sec)
mysql> call my_prc(2,4)##
+------+
| SUM |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
5、IF条件控制语句
mysql> create procedure my_prc(a int, b int)
-> begin
-> IF (a > b) THEN
-> select a as MAX;
-> ELSE
-> select b as MAX;
-> END IF;
-> end##
Query OK, 0 rows affected (0.00 sec)
mysql> call my_prc(2,4)
-> ##
+------+
| MAX |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
6、CASE条件控制语句
mysql> create procedure my_prc(a int, b int, ope varchar(1))
-> begin
-> CASE ope
-> WHEN '+' THEN select a+b as '和';
-> WHEN '-' THEN select a-b as '差';
-> WHEN '*' THEN select a*b as '积';
-> WHEN '/' THEN select a/b as '商';
-> END CASE;
-> end##
Query OK, 0 rows affected (0.00 sec)
mysql> call my_prc(2,4,'-')##
+------+
| 差 |
+------+
| -2 |
+------+
1 row in set (0.04 sec)
7、LOOP循环控制语句
mysql> create procedure my_prc(a int)
-> begin
-> label : LOOP
-> set a = a + 1;
-> IF (a < 10)
-> THEN select 'first label';
-> ELSE
-> ITERATE label;
-> END IF;
-> LEAVE label;
-> END LOOP label;
-> end##
Query OK, 0 rows affected (0.00 sec)
mysql> call my_prc(5)##
+-------------+
| first label |
+-------------+
| first label |
+-------------+
1 row in set (0.00 sec)
8、WHILE语句
mysql> create procedure my_prc()
-> begin
-> declare xx INT DEFAULT 10;
-> WHILE xx > 0 DO
-> set xx = xx - 1;
-> END WHILE;
-> set @vx = xx;
-> end##
Query OK, 0 rows affected (0.03 sec)
mysql> call my_prc()##
Query OK, 0 rows affected (0.00 sec)
mysql> select @vx##
+------+
| @vx |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
/*****************************************************************************/
/*************************触发器******************************************/
一、创建触发器
1、BEFORE INSERT型触发器
mysql> delimiter ##
mysql> create table hjf.logtab
-> (id int not null auto_increment primary key,
-> oname varchar(20),
-> otime varchar(30))##
Query OK, 0 rows affected (0.01 sec)
mysql> create trigger hjf.fstin
-> before insert ON newInfo
-> for each row
-> begin
-> insert into logtab(oname, otime) value('hjf', SYSDATE());
-> end##
Query OK, 0 rows affected (0.43 sec)
mysql> insert into hjf.newInfo values(12, 'Naic', 23.12, 2, 3)##
Query OK, 1 row affected (0.03 sec)
mysql> select * from hjf.logtab##
+----+-------+---------------------+
| id | oname | otime |
+----+-------+---------------------+
| 1 | hjf | 2017-04-08 20:18:01 |
+----+-------+---------------------+
1 row in set (0.00 sec)
mysql> select * from newInfo where id = 12##
+----+------+-------+-----------+-----------+
| id | name | score | subjectid | teacherid |
+----+------+-------+-----------+-----------+
| 12 | Naic | 23.12 | 2 | 3 |
+----+------+-------+-----------+-----------+
1 row in set (0.00 sec)
2、AFTER INSERT型触发器
mysql> create trigger hjf.secinsertrg
-> after insert ON newInfo
-> for each row
-> begin
-> insert into logtab(oname, otime) value('hjf_after', SYSDATE());
-> end##
Query OK, 0 rows affected (0.01 sec)
mysql> insert into hjf.newInfo values(14, 'Maic', 23.12, 2, 3)##
Query OK, 1 row affected (0.00 sec)
mysql> select * from logtab##
+----+-----------+---------------------+
| id | oname | otime |
+----+-----------+---------------------+
| 1 | hjf | 2017-04-08 20:18:01 |
| 2 | hjf | 2017-04-08 20:27:32 |
| 3 | hjf_after | 2017-04-08 20:27:32 |
+----+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from newInfo where id = 14##
+----+------+-------+-----------+-----------+
| id | name | score | subjectid | teacherid |
+----+------+-------+-----------+-----------+
| 14 | Maic | 23.12 | 2 | 3 |
+----+------+-------+-----------+-----------+
1 row in set (0.00 sec)
二、删除触发器
mysql> drop trigger hjf.secinsertrg##
Query OK, 0 rows affected (0.00 sec)
三、修改触发器
mysql> drop trigger hjf.secinsertrg##
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger hjf.secinsertrg
-> before delete ON newInfo
-> for each row
-> begin
-> insert into logtab(oname, otime) value('hjf_after', SYSDATE());
-> end##
Query OK, 0 rows affected (0.01 sec)
/*****************************************************************************/
阅读全文
0 0
- 操作mysql
- mysql 操作
- MYSQL操作
- mysql 操作
- MySQL操作
- MYSQL操作
- mysql 操作
- mysql操作
- MYSQL操作
- mysql操作
- mysql操作
- MYSQL操作
- mysql 操作
- 操作mysql
- mysql操作
- mysql操作!
- Mysql----操作
- mysql操作
- uvalive 4329 Ping Pong（树状数组）
- Uboot到底如何启动内核
- 距离变换distanceTransform应用——寻找图像的质心
- Python-numpy
- Easyui-combobox使用
- MySql操作
- 数据库设计
- 清晰明了告诉你RequireJS 的使用。
- bootstrap中多个modal之间的切换
- 安装以及配置Maven
- 懒虫小鑫
- MySQ四种L事务隔离级别
- DSP程序中段的定义和作用
- 欧拉函数的延伸：关于求小于等于n且与n互质的数的和。
