SQL(转)

2017-01-12 09:52:57来源:oschina作者:mifans人点击

/**
QQ:503887183
*/
/**
SQL:(Structured Query Language)简称SQL,结构化查询语言,数据库管理系统通过sql语言来管理数据库中的数据。
DDL:(Data Defination Language)数据定义语言,用户定义数据库、表、试图、索引、触发器、存储过程、定时任务等,像Drop、Create、Alter等语句。
DML:(Data Manipulation Language)数据操作语言,用于操作表中的数据,像insert、delete、update
DQL:(Data Query Language)数据检索语言,用于检索数据表中的数据,像select
DCL:(Data Control Language)数据控制语言,用于控制用户的访问权限,Grant、Revoke、Commit、Rollback等。
*/
/****************************基础篇*******************************/
SELECT now();
SELECT user();
SELECT database();
SELECT version();
SELECT UUID();
SELECT length(uuid());
SELECT length(uuid_short());
SHOW TABLES FROM mysql ;
SHOW COLUMNS FROM user1;
show processlist;
CREATE SCHEMA IF NOT EXISTS data1 DEFAULT CHARACTER SET =UTF8;
CREATE SCHEMA IF NOT EXISTS data2 DEFAULT CHARACTER SET =UTF8;
SHOW WARNINGS ;
SHOW CREATE SCHEMA data1;
ALTER DATABASE data1 DEFAULT CHARACTER SET =GBK;
CREATE TABLE aaa(
aa1 TINYINT,
aa2 TINYINT,
PRIMARY KEY (aa1,aa2)
);
/*创建用户表*/
CREATE TABLE IF NOT EXISTS `user1`(
id SMALLINT,
username VARCHAR(20),
age TINYINT,
sex ENUM('男','女','保密'),
email VARCHAR(50),
address VARCHAR(200),
birth YEAR,
salart FLOAT(8,2),
tel INT,
married TINYINT(1) COMMENT '0代表未婚,1代表已婚'
)ENGINE =INNODB CHARSET =UTF8;
/*ENUM('VALUE1','VALUE2','VALUE3') 每次只能选一个*/
/*SET('VALUE1','VALUE2','VALUE3') 每次可以选择多个*/
SHOW INDEXES FROM user1;
/*查看用户表*/
SELECT * FROM ms1.user1;
/*查看系统用户表信息*/
SELECT user,host,password FROM mysql.`user`;
/*重刷用户表使数据库重新使用user用户表信息*/
FLUSH PRIVILEGES ;
/*查看数据库版本和当前日期*/
SELECT version(),current_date();
/*查看当前用户和当前日期*/
SELECT user(),current_date();
/*创建存储过程*/
CREATE PROCEDURE sum(IN num1 TINYINT,OUT sum TINYINT)
BEGIN
DECLARE st1 CURSOR FOR SELECT * FROM ms1.user1;
DECLARE st2 CURSOR FOR SELECT * FROM user1;
END;
CREATE PROCEDURE sum1(IN num1 TINYINT,OUT sum TINYINT)
BEGIN
DECLARE st1 CURSOR FOR SELECT * FROM ms1.user1;
DECLARE st2 CURSOR FOR SELECT * FROM user1;
END;
SELECT year(current_date),month(current_date),day(current_date),hour(current_timestamp);
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
SELECT 1 IS NULL, 1 IS NOT NULL;
SELECT database();
SHOW VARIABLES LIKE '%HAVE%';
SHOW VARIABLES ;
SHOW VARIABLES LIKE 'auto%';
/*慢查日志*/
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE '%log%';
/*设置编码方式*/
SHOW VARIABLES LIKE '%char%';
SET CHARACTER_SET_SERVER =utf8;
SET CHARACTER_SET_CLIENT =utf8;
/*会话变量*/
SHOW SESSION VARIABLES LIKE 'auto%';
SHOW GLOBAL VARIABLES LIKE 'auto%';
SELECT length("*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9");
SELECT password("123456");
SELECT user,host,password FROM mysql.user;
SELECT * FROM mysql.user FOR UPDATE ;
/*创建用户方式1:*/
GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' IDENTIFIED BY '123456'
WITH GRANT OPTION ;
GRANT RELOAD ,PROCESS ON *.* TO 'mysql1'@'%';
DELETE FROM mysql.user WHERE user='mysql1';
GRANT USAGE ON *.* TO 'admin'@'%';/*USAGE代表只是可以连接的账户,没有任何权限*/
USE mysql;
/*创建用户方式2:尝试过后全都有问题,有很多需要默认值*/
INSERT INTO user VALUES ('localhost','monty',password('123456'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user SET host='localhost',user='admin',reload_priv='Y', process_priv='Y';
INSERT INTO mysql.user (host,user,password,ssl_cipher) VALUES('localhost','dummy','','Y');
FLUSH PRIVILEGES;
DESC mysql.user;
DESC ms1.user1;
SELECT host,user,password FROM mysql.user;
GRANT SELECT ON *.* TO lyzh@localhost IDENTIFIED BY '123456' WITH GRANT OPTION ;
GRANT SELECT ON *.* TO lyzh@'%' IDENTIFIED BY '123456' WITH GRANT OPTION ;
/*目前只有select权限 需要REVOKE重新授权*/
REVOKE SELECT on *.* FROM lyzh@'%';
GRANT ALL ON *.* TO lyzh@localhost IDENTIFIED BY '123456' WITH GRANT OPTION ;
GRANT ALL PRIVILEGES ON *.* TO lyzh1@'%' IDENTIFIED BY '123456' WITH GRANT OPTION ;
/*WITH GRANT OPTION表示这个帐号可以对其他的帐号赋予一定的权限*/
FLUSH PRIVILEGES ;
/*给数据库建立授权用户*/
CREATE DATABASE database1;
GRANT SELECT ,INSERT ,UPDATE ,DELETE ,CREATE ,DROP ON database1.* TO 'database1'@'%' IDENTIFIED BY '123456';
CREATE DATABASE ms1 CHARACTER SET =UTF8;
/*修改账户密码和权限*/
SELECT user,host,password FROM mysql.user;
SELECT * FROM mysql.user;
/*mysqladmin -u admin -h % password "newpwd"*/
SET PASSWORD FOR 'admin'@'%' = PASSWORD ('1234567');
/*复制表*/
CREATE TABLE AAA LIKE mysql.user ;
SELECT * INTO BBB FROM mysql.user;
SELECT * FROM BBB;
DROP TABLE AAA;
#刚安装好的数据库密码默认为空,所以要设置
#mysqladmin -uroot PASSWORD '123456'SHOW PROCESSLIST ;
/*在创建数据库的时候指定编码方式*/
CREATE DATABASE ms2 DEFAULT CHARSET ='UTF8';
CREATE DATABASE ms3 DEFAULT CHARACTER SET='UTF8';
/*查看所选择的数据库*/
SELECT database();
/*删除表*/
DROP TABLE datatype1;
/*创建表*/
CREATE TABLE `datatype1`(
data1 BIT,
data2 TINYINT,
data3 SMALLINT,
data4 MEDIUMINT,
data5 INT,
data6 BIGINT
)ENGINE =INNODB CHARACTER SET ='UTF8';
INSERT INTO datatype1(data1, data2, data3, data4, data5, data6)
VALUES (1,-128,-32768,-8388608,-2147483648,-9223372036854775808);
SELECT * FROM datatype1;
/*unsigned代表无符号,范围扩大了一倍,比如tinyint 从-128~127,那么就可以存储为0-255了*/
CREATE TABLE `datatype2`(
data1 TINYINT UNSIGNED,
data2 TINYINT
)ENGINE =INNODB CHARACTER SET ='UTF8';
INSERT INTO datatype2(data1, data2) VALUE (255,127);
/*zerofill为0填充*/
DROP TABLE datatype3;
CREATE TABLE `datatype3`(
data1 TINYINT(3) ZEROFILL,
data2 TINYINT
)ENGINE =INNODB CHARACTER SET ='UTF8';
INSERT INTO datatype3(data1, data2) VALUE (1,1);
SELECT * FROM datatype3;/*测试浮点类型*/
CREATE TABLE `datatype4`(
data1 FLOAT,
data2 DOUBLE,
data3 DECIMAL
)ENGINE =INNODB CHARSET ='UTF8';
INSERT INTO datatype4 VALUE (3.1415,3.1415,3.1415);
/*总结:UNSIGNED 代表无符号,ZEROFILL代表0填充*/
CREATE TABLE `datatype5`(
data1 CHAR(4),
data2 VARCHAR(4)
);
INSERT INTO datatype5 VALUE (1111,1111);
CREATE TABLE `datatype6`(
data1 TEXT
);
INSERT INTO datatype6 VALUE ('sdrwerwesdfsdf电风扇地方');
SELECT *
FROM datatype6;
/*性能来说:char>varchar>text*/CREATE TABLE `datatype7`(
sex ENUM('m','w','mw'),
fav SET('a','b','c','d')
);
INSERT INTO datatype7 VALUE ('mw','a,b,c,d');
INSERT INTO datatype7 VALUE ('mw',3);
SELECT * FROM datatype7;
/*主键*/
/**
每个表只能有一个主键,但是可以有复合主键(代表的是把一个主键拆分成多个,同时来约束)
*/
SHOW CREATE DATABASE ms1;
SHOW CREATE TABLE datatype7;
CREATE TABLE datatype8(
id TINYINT PRIMARY KEY ,
name CHAR(20)
);
SHOW CREATE TABLE datatype8;
DESC datatype8;
/*自增长 AUTO_INCREMENT*/
CREATE TABLE datatype9(
id TINYINT PRIMARY KEY AUTO_INCREMENT ,
name CHAR(20)
);
CREATE TABLE datatype10(
id TINYINT PRIMARY KEY AUTO_INCREMENT ,
name CHAR(20)
)ENGINE =INNODB AUTO_INCREMENT=100 DEFAULT CHARSET = 'UTF8';
DELETE FROM datatype9;
INSERT INTO datatype9 VALUE (1,'lyzh');
INSERT INTO datatype9(name) VALUE ('lyzh');
SELECT * FROM datatype9;
/*修改自增长*/
ALTER TABLE datatype9 AUTO_INCREMENT = 500;
/*表结构*/
DESC datatype10;
/*建表语句*/
SHOW CREATE TABLE datatype10;
/*默认值*/
DROP TABLE datatype11;
CREATE TABLE datatype11(
data1 TINYINT UNSIGNED PRIMARY KEY NOT NULL DEFAULT 10,
data2 CHAR(20) DEFAULT '哗哗哗的流泪',
data3 DATETIME
)ENGINE =INNODB AUTO_INCREMENT=10 CHARSET ='UTF8';
INSERT INTO datatype11(data3) VALUE (current_date);
INSERT INTO datatype11(data1,data3) VALUE (20,current_date);
SELECT * FROM datatype11;
SHOW CREATE TABLE datatype11;
DESC datatype11;
/*唯一约束 用来限制非主键列值的唯一性*/
CREATE TABLE datatype12(
data1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
data2 CHAR(20) UNIQUE KEY
);
INSERT INTO datatype12(data2) VALUE ('lyzh1');
SELECT * FROM datatype12;
/**
约束类型:
1、NOT NULL(非空约束)
2、PARIMARY KEY(主键约束)
3、UNIQUE KEY(唯一约束)
4、DEFAULT(默认约束)
5、FOREIGN KEY(外键约束)
*/
/*创建表规则*/
/*
CREATE TABLE IF NOT EXISTS datatype13(
字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT 默认值] [PRIMARY KEY|UNSIGNED KEY] [AUTO_INCREMENT]
)ENGINE =INNODB CHARSET ='UTF8'|UTF8 AUTO_INCREMENT=100;
*/
CREATE TABLE IF NOT EXISTS `datatype13`(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE ,
password CHAR(32) NOT NULL ,
email VARCHAR(50) NOT NULL DEFAULT '503887183@qq.com',
age TINYINT UNSIGNED DEFAULT 27,
sex ENUM('男','女','保密') DEFAULT '保密',
addr VARCHAR(200) NOT NULL DEFAULT '西安',
salary FLOAT(10,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'user/default.jpg'
)ENGINE =INNODB CHARSET ='UTF8' AUTO_INCREMENT=100;SHOW CREATE TABLE datatype13;
DESCRIBE datatype13;/*修改表名称*/
ALTER TABLE datatype13 RENAME TO datatype131;
ALTER TABLE datatype131 RENAME AS datatype13;
RENAME TABLE datatype7 TO datatype71;
RENAME TABLE datatype71 TO datatype7;
/*添加字段*/
SELECT * FROM datatype7;
ALTER TABLE datatype7 ADD sex1 ENUM('男','女') DEFAULT '女';
ALTER TABLE datatype7 ADD col1 ENUM('男','女') DEFAULT '女' AFTER fav;
ALTER TABLE datatype7 ADD col2 ENUM('男','女') DEFAULT '女' FIRST ;
ALTER TABLE datatype7 ADD col10 VARCHAR(50) FIRST ;
ALTER TABLE datatype7 ADD pid VARCHAR(50) FIRST ;
ALTER TABLE datatype7
ADD col3 ENUM('男','女') AFTER col2,
ADD col4 FLOAT(6,2),
ADD col5 SET('A','B') ;
/*删除字段*/
ALTER TABLE datatype7 DROP col5;
ALTER TABLE datatype7
DROP col5,
DROP col3,
DROP col4;
/*修改字段*/
ALTER TABLE datatype7 MODIFY col10 VARCHAR(100);/*会把字段的其他属性设置为默认了,所以需要再把原来的加上*/
DESC datatype7;
ALTER TABLE datatype7 CHANGE col10 col11 VARCHAR(300);
/*修改默认值*/
ALTER TABLE datatype7 ALTER col11 SET DEFAULT 'lyzh';
ALTER TABLE datatype7 ALTER col11 DROP DEFAULT ;
ALTER TABLE datatype7 MODIFY pid VARCHAR(200);
SELECT * FROM datatype7;
ALTER TABLE datatype7 ADD PRIMARY KEY(pid);
/*24*/
SELECT * FROM mysql.user;SELECT * FROM datatype;
INSERT INTO datatype VALUE (12,12,12,12,12);
TRUNCATE TABLE datatype;CREATE TABLE IF NOT EXISTS datatype14(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL ,
email VARCHAR(30) NOT NULL DEFAULT '503887183@qq.com',
age TINYINT UNSIGNED DEFAULT 27
)ENGINE =INNODB AUTO_INCREMENT=100 CHARSET =UTF8;
SELECT * FROM datatype14;
INSERT INTO datatype14(username, email, age) VALUES ('lyzh7','503887183@qq.com',DEFAULT ),('lyzh2','503887183@qq.com',DEFAULT ),('lyzh3','503887183@qq.com',DEFAULT ),('lyzh4','503887183@qq.com',DEFAULT ),('lyzh5','503887183@qq.com',DEFAULT ),('lyzh6','503887183@qq.com',DEFAULT );
INSERT INTO datatype14(username, email, age) VALUES ('lyzh8','503887183@qq.com',NULL );
INSERT INTO datatype14(username, email, age,pId) VALUES ('悟空','285958092@qq.com',29,120 );
INSERT INTO datatype14(username, email, age,pId) VALUES ('八戒','285958092@qq.com',29,120);
/*逻辑运算*/
SELECT * FROM datatype14 WHERE age IS NULL ;
SELECT * FROM datatype14 WHERE age <=> NULL ;
SELECT * FROM datatype14 WHERE id BETWEEN 101 AND 104;
/*查询用户名为四位的用户,这个特么的竟然用的是下划线,劳资也是醉了*/
SELECT * FROM datatype14 WHERE username LIKE '____';
/*聚合函数*/
ALTER TABLE datatype14 ADD COLUMN sex ENUM('男','女','保密') DEFAULT '男';
SELECT * FROM datatype14 GROUP BY sex;
/*查询分组后用每个组用户名连接的结果*/
SELECT id,email,age,GROUP_CONCAT(username) FROM datatype14 GROUP BY sex;
SELECT id,GROUP_CONCAT(email),age,GROUP_CONCAT(username) FROM datatype14 GROUP BY sex;
ALTER TABLE datatype14 ADD COLUMN regTime TIMESTAMP DEFAULT now();
ALTER TABLE datatype14 ADD COLUMN pId TINYINT AFTER age;
ALTER TABLE datatype14 MODIFY COLUMN pId TINYINT DEFAULT 100;
SELECT count(*) FROM datatype14 GROUP BY sex;
/*with rollup,新增一行把各列进行统计*/
SELECT id,sex,GROUP_CONCAT(username),COUNT(*),MAX(age),MIN(age),AVG(age),COUNT(age) FROM datatype14 GROUP BY sex WITH ROLLUP ;
/*
分组查询一般也配合这分组详情group_concat,聚合函数各种,with rollup等配合使用
*/
/*having 子句*/
SELECT sex,GROUP_CONCAT(username),COUNT(*),MAX(age),MIN(age) FROM datatype14 GROUP BY sex WITH ROLLUP ;
SELECT sex,GROUP_CONCAT(username),COUNT(*),MAX(age),MIN(age) FROM datatype14 GROUP BY sex HAVING COUNT(*)>2 AND MAX(age)>28;
SELECT sex,GROUP_CONCAT(username),COUNT(*),MAX(age) max_age,MIN(age) FROM datatype14 GROUP BY sex HAVING COUNT(*)>2 AND max_age>28;/*连接*/
CREATE TABLE IF NOT EXISTS prov(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT '陕西'
)ENGINE =INNODB AUTO_INCREMENT=100 CHARSET =UTF8;
INSERT INTO prov(name) VALUES ('陕西'),('吉林'),('北京'),('广州'),('上海');
INSERT INTO prov(name) VALUES ('西安'),('延安'),('宝鸡'),('山西'),('临潼');
SELECT * FROM prov;
/*连接*/
CREATE TABLE IF NOT EXISTS prov1(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT '陕西'
)ENGINE =INNODB AUTO_INCREMENT=100 CHARSET =UTF8;
INSERT INTO prov1(name) VALUES ('陕西'),('吉林'),('北京'),('广州'),('上海');
INSERT INTO prov1(name) VALUES ('西安'),('延安'),('宝鸡'),('山西'),('临潼');
SELECT * FROM prov1;
COMMIT ;SELECT * FROM datatype14;
/*一般的*/
SELECT d.id,d.username,p.id,p.name FROM datatype14 d,prov p WHERE d.pId = p.id;
/*内连接 加入的 先选一张表,然后加入另一张,内连接,如果有多张表。继续jion就行了,内连接只能查询符合条件的数据*/
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 AS d INNER JOIN prov AS p ON d.pId=p.id;
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 d INNER JOIN prov p ON d.pId=p.id;
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 d CROSS JOIN prov p ON d.pId=p.id;
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 d CROSS JOIN prov p ON d.pId=p.id WHERE d.sex='男';
SELECT d.id,d.username,d.email,d.sex,p.name,COUNT(*),GROUP_CONCAT(d.username) FROM datatype14 d CROSS JOIN prov p ON d.pId=p.id WHERE d.sex='男' GROUP BY sex;
SELECT d.id,d.username,d.email,d.sex,p.name,COUNT(*),GROUP_CONCAT(d.username) FROM datatype14 d CROSS JOIN prov p ON d.pId=p.id GROUP BY sex;
SELECT d.id,d.username,d.email,d.sex,p.name,COUNT(*),GROUP_CONCAT(d.username) FROM datatype14 d CROSS JOIN prov p ON d.pId=p.id GROUP BY sex HAVING COUNT(*)>1;
/*外连接,先显示其中的全部数据,然后显示另一张表中复合条件的数据*/
INSERT INTO datatype14(username,pId) VALUES ('lyzh9',105);
SELECT * FROM datatype14;
SELECT * FROM prov;
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 AS d INNER JOIN prov AS p ON d.pId=p.id;
/*先查左表(主表)数据,然后把右表中复合条件都的数据显示出来*/
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 AS d LEFT JOIN prov AS p ON d.pId=p.id;
/*可以使用外连接来代替优化in查询*/
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 AS d LEFT JOIN prov AS p ON d.pId=p.id WHERE p.name IS NOT NULL ;
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 AS d RIGHT JOIN prov AS p ON d.pId=p.id;
/*可以使用外连接来代替优化in查询*/
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 AS d RIGHT JOIN prov AS p ON d.pId=p.id WHERE d.username IS NOT NULL ;
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 AS d LEFT JOIN prov AS p ON d.pId=p.id
UNION ALL
SELECT d.id,d.username,d.email,d.sex,p.name FROM datatype14 AS d RIGHT JOIN prov AS p ON d.pId=p.id;/*外键*/
CREATE TABLE IF NOT EXISTS `department`(
id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
depName VARCHAR(50) NOT NULL UNIQUE KEY
)ENGINE =INNODB;
INSERT INTO department(depName) VALUES ('教学部'),('市场部'),('运营部'),('督导部');
SELECT * FROM department ORDER BY id;
CREATE TABLE IF NOT EXISTS `employ`(
id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
username VARCHAR(50) NOT NULL UNIQUE KEY ,
depId TINYINT UNSIGNED,
INDEX dep_ind(depId),
FOREIGN KEY (depId) REFERENCES department(id)
/*FOREIGN KEY (depId) REFERENCES department(id) ON UPDATE CASCADE ON DELETE RESTRICT*/
)ENGINE =INNODB;
INSERT INTO employ(username, depId) VALUES ('lyzh1',1),('lyzh2',2),('lyzh3',4),('lyzh4',4);
INSERT INTO employ(username, depId) VALUES ('lyzh5',3),('lyzh6',2),('lyzh7',1),('lyzh8',4);
INSERT INTO employ(username, depId) VALUES ('lyzh9',3),('lyzh10',2),('lyzh11',1),('lyzh12',4);
/*第五个就插入不进去了*/
INSERT INTO employ(username, depId) VALUES ('lyzh1',5);
SELECT d.id,d.depName,e.username FROM employ e INNER JOIN department d ON e.depId = d.id;
DELETE FROM department WHERE id=4;
DESC employ;
SHOW CREATE TABLE employ;
/*系统自动生成的建表语句*/
CREATE TABLE `employ2` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`depId` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `dep_ind` (`depId`),
CONSTRAINT `employ_ibfk_2` FOREIGN KEY (`depId`) REFERENCES `department` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
SELECT *FROM employ2 ;
ALTER TABLE employ2 DROP FOREIGN KEY employ_ibfk_2;
/*联合查询*/
SELECT e1.username FROM employ e1 UNION SELECT username FROM employ2 e2;/*子查询*/
CREATE TABLE IF NOT EXISTS `student`(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE KEY ,
score TINYINT UNSIGNED
)ENGINE =INNODB CHARACTER SET =UTF8;
INSERT INTO student (username, score) VALUES ('lyzh1',20),('lyzh2',35),('lyzh3',60),('lyzh4',70),('lyzh5',80),('lyzh6',90),('lyzh7',95);
SELECT * FROM student;
CREATE TABLE IF NOT EXISTS `schoolship`(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,
level TINYINT UNSIGNED
)ENGINE =INNODB CHARACTER SET =UTF8;
INSERT INTO schoolship(level) VALUES (55),(65),(75),(85),(95);
SELECT * FROM schoolship;
/*in =() any some all exits*/
/**
子查询
where 型子查询:内层sql的返回值在where后作为条件表达式的一部分
例句: select * from tableA where colA = (select colB from tableB where ...);
from 型子查询:内层sql查询结果,作为一张表,供外层的sql语句再次查询
例句:select * from (select * from ...) as tableName where ....
*/
SELECT s.id,s.username,s.score FROM student s WHERE s.score >(SELECT level FROM schoolship ss WHERE ss.id=1);
SELECT * FROM student s WHERE s.score >=ANY (SELECT level FROM schoolship ss);
SELECT * FROM student s WHERE s.score >=SOME (SELECT level FROM schoolship ss);
SELECT * FROM student s WHERE s.score >=ALL (SELECT level FROM schoolship ss);/*正则表达式*/
SELECT * FROM datatype14;
/*以t开头*/
SELECT * FROM datatype14 WHERE username REGEXP '^t';
/*以m结束*/
SELECT * FROM datatype14 WHERE username REGEXP 'm$';
/*.为任意字符*/
SELECT * FROM datatype14 WHERE username REGEXP 'l....u';
SELECT * FROM datatype14 WHERE username REGEXP 'l.u';
/**/
SELECT * FROM datatype14 WHERE username LIKE 'l____u';
/*字符集合*/
SELECT * FROM datatype14 WHERE username REGEXP '[on]';
SELECT * FROM datatype14 WHERE username REGEXP '[^o]';SELECT ceil(1.2),ceiling(1.2);
SELECT floor(1.2);
SELECT MOD(10,2);
SELECT pow(2,3);
SELECT power(2,3);
SELECT truncate(3.1415926,2);
SELECT abs(-3.24);
SELECT rand()*pow(10,20);
SELECT rand(10);
/*存储过程*/
SHOW VARIABLES LIKE '%fun%';
SHOW VARIABLES LIKE '%pro%';
CREATE PROCEDURE pro1() SELECT version();
CALL pro1();
CREATE PROCEDURE pro2(IN id SMALLINT UNSIGNED)
BEGIN
DELETE FROM user1 WHERE id=id;
END;
CALL pro2(1);
CREATE PROCEDURE pro3()
BEGIN
DECLARE asd TINYINT DEFAULT 120;
SELECT asd;
END;
CALL pro3();
/*DELIMITER $$;*/
CREATE PROCEDURE pro4()
BEGIN
DECLARE asd TINYINT DEFAULT 120;
SET asd=121;
SELECT asd;
END;
CALL pro4();
CREATE PROCEDURE pro5(IN parm1 INT UNSIGNED)
BEGIN
SELECT parm1;
SET parm1 = parm1+1;
SELECT parm1;
END;
CALL pro5(5);
SET @param1 = 2;
CALL pro5(@param1);
SELECT @param1;
CREATE PROCEDURE pro6(IN param1 INT UNSIGNED,OUT param2 INT UNSIGNED)
BEGIN
SELECT param1;
SELECT param2;
SET param2 = param1*10;
END;
SET @param1 = 5;
SET @param2 = 0;
CALL pro6(@param1,@param2);
SELECT @param2;
CREATE PROCEDURE pro7(INOUT param1 INT UNSIGNED)
BEGIN
SET param1 = param1-100;
END;
SET @param1 = 10;
CALL pro7(@param1);
SELECT @param1;
/*流程控制*/
CREATE PROCEDURE pro8(IN age INT UNSIGNED)
BEGIN
IF (age<18) THEN
SELECT '未成年';
ELSEIF (age=18)THEN
SELECT '成年人';
ELSE
SELECT '老年了你已经';
END IF;
END;
CALL pro8(15);
CALL pro8(18);
CALL pro8(35);
CREATE PROCEDURE pro9(IN age INT UNSIGNED)
BEGIN
CASE age
WHEN 25 THEN
SELECT '25';
WHEN 28 THEN
SELECT '28';
ELSE
SELECT 'more';
END CASE ;
END;
CALL pro9(25);
CALL pro9(28);
CALL pro9(50);
SELECT * FROM datatype14;
SELECT username,email,
(CASE sex
WHEN '男' THEN 1
WHEN '女' THEN '0'
ELSE '2'
END)
FROM datatype14;
SELECT IFNULL(username,"NULL"),email,
(CASE sex
WHEN '男' THEN 1
WHEN '女' THEN '0'
ELSE '2'
END)
FROM datatype14;
CREATE PROCEDURE pro10()
BEGIN
DECLARE i INT UNSIGNED DEFAULT 1;
DECLARE result INT UNSIGNED DEFAULT 0;
WHILE i<=100 DO
SET result = result +i;
SET i=i+1;
END WHILE;
SELECT result;
END;
CALL pro10();
CREATE TABLE IF NOT EXISTS datatype15(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
sex CHAR(2) DEFAULT '1'
)ENGINE =INNODB AUTO_INCREMENT=1 CHARACTER SET =UTF8;
CREATE PROCEDURE pro11()
BEGIN
DECLARE i INT UNSIGNED DEFAULT 0;
WHILE i<=100 DO
INSERT INTO datatype15(username, sex) VALUE (concat("lyzh",i),'M');
SET i = i+1;
END WHILE;
END;
CALL pro11();
DELETE FROM datatype15;
DROP PROCEDURE IF EXISTS pro11;
SELECT * FROM datatype15;
/*while测试*/
CREATE PROCEDURE pro12()
BEGIN
DECLARE i INT UNSIGNED DEFAULT 100;
WHILE i<=200 DO
IF (i%2=0) THEN
UPDATE datatype15 SET sex = 'F' WHERE id=i;
END IF;
SET i = i+1;
END WHILE;
END;
CALL pro12();
SELECT * FROM datatype15;
/*repeat测试*/
CREATE PROCEDURE pro13()
BEGIN
DECLARE i INT UNSIGNED DEFAULT 100;
REPEAT
IF (i%3=0) THEN
UPDATE datatype15 SET sex = 'S' WHERE id=i;
END IF;
SET i=i+1;
UNTIL i>300 END REPEAT;
END;
CALL pro13();
SELECT * FROM datatype15;
/*loop测试*/
DROP PROCEDURE pro14;
CREATE PROCEDURE pro14()
BEGIN
DECLARE i INT UNSIGNED DEFAULT 100;
myloop:LOOP
IF (i%4=0) THEN
UPDATE datatype15 SET username = concat("lyzh4",i) WHERE id=i;
END IF;
SET i=i+1;
IF i>200 THEN
LEAVE myloop;
END IF;
END LOOP;
END;
CALL pro14();
SELECT * FROM datatype15;
/*根据主键重复值代码跳过错误执行后面正确的*/
CREATE PROCEDURE pro15()
BEGIN
INSERT INTO datatype15(id,username,sex) VALUE (101,"lyzh11111","S");
INSERT INTO datatype15(id,username,sex) VALUE (203,"lyzh11111","S");
END;
CALL pro15();
DROP PROCEDURE pro15;
CREATE PROCEDURE pro15()
BEGIN
/*定义错误代码*/
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @num=1;
INSERT INTO datatype15(id,username,sex) VALUE (101,"lyzh11111","S");
INSERT INTO datatype15(id,username,sex) VALUE (204,"lyzh11111","S");
END;
CALL pro15();
/*管理存储过程*/
SHOW PROCEDURE STATUS WHERE Db='ms1';
/*查看创建语句*/
SHOW CREATE PROCEDURE pro10;
SHOW CREATE TABLE datatype15;
/*游标*/
DROP PROCEDURE IF EXISTS pro16;
CREATE PROCEDURE pro16()
BEGIN
DECLARE down INT DEFAULT 0;
DECLARE a INT;
DECLARE b VARCHAR(50);
DECLARE cur1 CURSOR FOR SELECT id,username FROM datatype15;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET down =1;
/*打开游标*/
OPEN cur1;
/*循环游标*/
REPEAT
/*读取游标*/
FETCH cur1 INTO a,b;
IF NOT down THEN
SELECT concat(a,concat(":",b));
END IF;
UNTIL down END REPEAT;
CLOSE cur1;
END;
CALL pro16;
CREATE PROCEDURE pro17()COMMENT '存储过程注释'
BEGIN
END;/*自定义函数*/
/*先查看是否具有创建函数的权限开启*/
SHOW VARIABLES LIKE '%func%';
/*如果为off,则需要开启,最好采用全局*/
SET GLOBAL log_bin_trust_function_creators = 1;
/*无参*/
CREATE FUNCTION date_format1() RETURNS VARCHAR(30)
RETURN date_format(now(),'%Y年%m月%d日 %H点:%i分:%s秒');
SELECT date_format1();
/*有参*/
CREATE FUNCTION avg1(num1 TINYINT UNSIGNED,num2 TINYINT UNSIGNED)RETURNS FLOAT(10,2)
RETURN (num1+num2)/2;
SELECT avg1(12,14);
/*操作数据表*/
DROP FUNCTION test1;
CREATE FUNCTION test1() RETURNS TINYINT
RETURN 12;
SELECT test1();
/*函数创建1:returns 返回值类型,函数体 begin end中间写执行体后return 对应类型的变量即可*/
CREATE FUNCTION fun1(a INT UNSIGNED,b INT UNSIGNED)
RETURNS INT UNSIGNED
BEGIN
RETURN a+b;
END;
SELECT fun1(1,2);
DROP FUNCTION IF EXISTS fun2;
CREATE FUNCTION fun2()
RETURNS BIGINT(50)
BEGIN
DECLARE down INT DEFAULT 0;
DECLARE abc INT DEFAULT 0;
DECLARE bbc BIGINT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT id FROM datatype15;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET down = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO abc;
IF NOT down THEN
SET bbc = bbc+abc;
END IF;
UNTIL down END REPEAT;
CLOSE cur1;
RETURN bbc;
END;
SELECT fun2();
SELECT *
FROM datatype15;
/*试图,创建试图的原因:简化sql查询操作*/
DROP VIEW IF EXISTS view1;
CREATE OR REPLACE VIEW view1 AS
SELECT * FROM datatype15;
SELECT * FROM view1;
SHOW TABLE STATUS FROM ms1;
SELECT * FROM mysql.user WHERE user='root';
SELECT drop_priv FROM mysql.user WHERE user='root';
SELECT create_priv FROM mysql.user WHERE user='root';
SHOW VARIABLES LIKE '%view%';
SHOW TABLES ;
/*
ALGORITHM = {
MERGE:merge混合的执行方式
TEMPTABLE:temptable存于临时表
UNDEFINED:undefined默认
}
试图也是一种表类型,是可以更新的,但是需要有权限
*/
CREATE OR REPLACE ALGORITHM = MERGE VIEW view2 AS
SELECT * FROM datatype15;
CREATE OR REPLACE ALGORITHM = MERGE VIEW view3 AS
SELECT * FROM datatype15 WITH CHECK OPTION ;
SELECT * FROM view2;
/*触发器*/
SELECT * FROM aaa;
SELECT * FROM prov;
DROP TRIGGER IF EXISTS tr_insert1;
/*after*/
CREATE TRIGGER tr_insert1 AFTER INSERT
ON aaa FOR EACH ROW
BEGIN
INSERT INTO prov(id,name) VALUE (111,'四川');
END;
INSERT INTO aaa VALUE (12,12);
/*before*/
DROP TRIGGER IF EXISTS tr_insert2;
CREATE TRIGGER tr_insert2 BEFORE UPDATE
ON aaa FOR EACH ROW
BEGIN
INSERT INTO prov(id,name) VALUE (112,'湖南');
END;
UPDATE aaa SET aa2 = 14 WHERE aa1=12;
SHOW TRIGGERS ;
/*复制表*/
CREATE TABLE tab2 LIKE tab1;/*锁:
1、表级锁:table-level locking:开销小、加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
2、行级锁:row-level locking:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。
3、页面锁:page-level locking:开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般。
仅从锁的角度来说:表级锁更适合以查询为主,只有少量按索引条件更新数据的应用,如web应用;
行级锁更适合于有大量按索引条件并发更新少量不同的数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。
*/
DROP TABLE tab1;
CREATE TABLE tab1(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL ,
birth TIMESTAMP DEFAULT now()
)ENGINE =INNODB AUTO_INCREMENT=1 CHARACTER SET =UTF8;
SHOW CREATE TABLE tab1;
ALTER TABLE tab1 ENGINE =MYISAM;
INSERT INTO tab1(username) VALUES ('lyzh1'),('lyzh2'),('lyzh3'),('lyzh4'),('lyzh5'),('lyzh6'),('lyzh7'),('lyzh8');
SELECT * FROM tab1;
/*创建共享读锁*/
SELECT * FROM tab1;
LOCK TABLES tab1 READ ;
UNLOCK TABLES;
SHOW VARIABLES LIKE '%table%';
SHOW STATUS LIKE '%table%';
/*加锁之后可以读,但是不可以写*/
SELECT * FROM tab1;
/*如果加了共享读锁就无法写入数据了,会一直等待,一直到解锁为止*/
UPDATE tab1 SET username='lyzh9' WHERE id=8;
/*创建共享写锁,就不可以读了,session会一直等待*/
LOCK TABLES tab1 WRITE ;
UNLOCK TABLES ;
/*并发锁*/
SHOW VARIABLES LIKE '%concurrent_insert%';
/*先设置并发插入的值为2*/
SET GLOBAL CONCURRENT_INSERT =2;
/*加入读锁并允许并发插入*/
LOCK TABLES tab1 READ LOCAL ;/*加锁之后可以读,但是不可以写*/
SELECT * FROM tab1;
/*如果加了共享读锁就无法写入数据了,会一直等待,一直到解锁为止*/
UPDATE tab1 SET username='lyzh9' WHERE id=8;
INSERT INTO tab1(username, birth) VALUES ('lyzh11',DEFAULT );/*事物*/
SHOW ENGINES ;
/*设置事物不是自动提交*/
SET AUTOCOMMIT =0;
START TRANSACTION ;
COMMIT ;/*慢查询 long_query_time满查询指定的时间,超过时间就是属于慢查询*/
SHOW VARIABLES LIKE '%long%';
SHOW STATUS LIKE '%uptime%';
/*设置慢查询为一秒后就会记录执行超过一秒的相关信息,ZY-slow.log文件*/
SET LONG_QUERY_TIME =1;
/*
启动慢查询方式1:安全模式启动
2:配置方式启动
*/
/*索引*/
CREATE TABLE test_index(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
email VARCHAR(200),
sex ENUM('男','女','保密') DEFAULT '男'
)DEFAULT CHAR SET =UTF8;
INSERT INTO test_index VALUE (DEFAULT ,'lyzh1','503887183@qq.com',DEFAULT );
INSERT INTO test_index VALUE (DEFAULT ,'lyzh2','285958092@qq.com',DEFAULT );
INSERT INTO test_index VALUE (DEFAULT ,'lyzh2','285958092@qq.com',DEFAULT );
INSERT INTO test_index VALUE (DEFAULT ,'lyzh3','285958092@qq.com',DEFAULT );
SHOW INDEXES FROM test_index;
SELECT * FROM test_index;
EXPLAIN SELECT * FROM test_index WHERE id=1;
/*
id目前为唯一索引以及主键约束
创建普通索引
*/
CREATE INDEX username ON test_index(username);
DROP INDEX username ON test_index;
CREATE INDEX email ON test_index(email);
SHOW CREATE TABLE test_index;
EXPLAIN SELECT username,email FROM test_index;
/*全文索引 只能在myisam存储引擎中使用 FULLTEXT就是全文索引*/
/**
索引:
提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑.
索引不是越多越好,一般我们在常出现于条件表达式中的列加索引.
值越分散的列,索引的效果越好
索引类型:
primary key主键索引
index 普通索引
unique index 唯一性索引
fulltext index 全文索引
*/
CREATE TABLE `test_index` (
`id` int(10) unsigned NOT NULL,
`username` varchar(50) DEFAULT NULL,
`username1` int(11) DEFAULT NULL,
`username2` int(11) DEFAULT NULL,
`username3` int(11) DEFAULT NULL,
`username4` int(11) DEFAULT NULL,
`username5` int(11) DEFAULT NULL,
`email` varchar(200) DEFAULT NULL,
`sex` enum('男','女','保密') DEFAULT '男',
KEY `email` (`email`),
KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE INDEX_TEST LIKE datatype14;
SHOW CREATE TABLE index_test;
SELECT * FROM index_test;
#重命名
ALTER TABLE INDEX_TEST RENAME index_test;
#添加主键索引
ALTER TABLE test_index ADD PRIMARY KEY(id);
#唯一索引
ALTER TABLE test_index ADD UNIQUE INDEX (username1);
#普通索引
ALTER TABLE test_index ADD INDEX (username2);
EXPLAIN test_index;
CREATE TABLE articles(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)ENGINE = MYISAM DEFAULT CHAR SET =UTF8;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
SELECT * FROM articles;
/*没有任何的索引*/
EXPLAIN SELECT * FROM articles;
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ('database');
/*全文索引比like效率高很多倍*/
EXPLAIN SELECT * FROM articles WHERE MATCH(title,body) AGAINST('database');
/*查询相似度*/
SELECT id, MATCH (title,body) AGAINST ('Tutorial') FROM articles;
/**
*/
ANALYZE TABLE articles;
CHECK TABLE articles;
OPTIMIZE TABLE articles;
SHOW TABLE STATUS ;SHOW VARIABLES LIKE '%partition%';#CREATE TABLE abc(id INT) PARTITION BY RANGE
SHOW PROCESSLIST ;
SELECT * FROM datatype14;
SELECT max(regTime) FROM datatype14;
SELECT min(regTime) FROM datatype14;
SELECT avg(age) FROM datatype14;
SELECT sum(age) FROM datatype14;
SELECT count(age) FROM datatype14;
CREATE TABLE goods (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR(50) NOT NULL ,
number INT UNSIGNED,
price FLOAT(8,2)
);
ALTER TABLE goods ADD COLUMN lanmu TINYINT UNSIGNED DEFAULT 1;
INSERT INTO goods (name, number, price) VALUES ('诺基亚',10,800.2),('三星',5,2300.1),('iphone',50,6400);
SELECT sum(number*price) FROM goods;
SELECT * FROM goods;
SELECT (number*price) as price FROM goods GROUP BY name;
SELECT COUNT(*) FROM goods;
SELECT sum(number) FROM goods GROUP BY lanmu;
SELECT max(price) FROM goods GROUP BY lanmu;
/*查询语句中的列就是变量,变量就可以运算*/
/*可以这样*/
SELECT id,name,number,price,lanmu FROM goods WHERE (goods.number-lanmu)>10;
/*但是不能这样*/
SELECT id,name,number,price,lanmu,(number-lanmu) as nl FROM goods WHERE nl>10;
/*但是呢,可以这样操作*/
SELECT id,name,number,price,lanmu,(number-lanmu) as nl FROM goods HAVING nl>10;
/*where针对的是磁盘的数据,加载到内存里面的结果集再进行筛选的话就需要having了*/
SELECT * FROM goods WHERE 1=1 HAVING id=3;
CREATE TABLE student1 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL ,
subject VARCHAR(30) NOT NULL ,
score INT UNSIGNED
);
INSERT INTO student1(id,name,subject,score) VALUES (DEFAULT ,'张三','数学',90),(DEFAULT ,'张三','语文',50),(DEFAULT ,'张三','地理',40);
INSERT INTO student1(id,name,subject,score) VALUES (DEFAULT ,'李四','语文',55),(DEFAULT ,'李四','政治',45);
INSERT INTO student1(id,name,subject,score) VALUES (DEFAULT ,'王五','政治',30);
INSERT INTO student1(id,name,subject,score) VALUES (DEFAULT ,'赵六','地理',85),(DEFAULT ,'赵六','八股文',90);
SELECT * FROM student1;
/*查询两门或者两门以上挂科的成绩平均值*/
#SELECT name ,avg(score) FROM student1 WHERE score<60 GROUP BY name HAVING count(id)>=2;
#SELECT name, avg(score) FROM student1 WHERE id IN (SELECT id FROM student1 WHERE score<60 GROUP BY name HAVING count(id)>=2) GROUP BY name;
#分析步骤1
#先求平均值分组
SELECT name,avg(score) FROM student1 WHERE score<=60 GROUP BY name;
/*count出问题了又*/
SELECT name,avg(score),count(score<=60) gk FROM student1 GROUP BY name HAVING gk>=2;
SELECT name,avg(score) as pj FROM student1 GROUP BY name;
SELECT name,subject,score,score<60 FROM student1;
#总结:先直达目标。然后进行筛选
SELECT name,sum(score<=60) as gk,avg(score) as pj FROM student1 GROUP BY name HAVING gk>=2;
SELECT DISTINCT (name) FROM student1;
SELECT DISTINCTROW (name) FROM student1;
/*DELIMITER */
SELECT sysdate();
SELECT now();
/*本月的最后一天*/
SELECT last_day(sysdate());
#日期
SELECT date(now());
#年月日时分秒毫秒
SELECT year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now()),microsecond(now());
#今年的第多少周
SELECT week(now());
SELECT date_format(now(),'%Y年%m月%d日 %h时:%i分:%s秒');
/**
yearYYYY 范围:1901~2155. 可输入值2位和4位(如98,2012)
dateYYYY-MM-DD 如:2010-03-14
timeHH:MM:SS 如:19:26:32
datetime YYYY-MM-DDHH:MM:SS 如:2010-03-14 19:26:32
timestampYYYY-MM-DDHH:MM:SS 特性:不用赋值,该列会为自己赋当前的具体时间
*/
SHOW CREATE FUNCTION date_format1;
SELECT to_days(now());
SELECT length(password(123456));
SELECT char(now());
#转换cast
SELECT cast(now() as CHAR);
SELECT char_length('25');
SELECT date(now());
#SELECT decode(1)
#复制表的两种办法
CREATE TABLE a as SELECT * FROM aaa;
CREATE TABLE b LIKE aaa;
SELECT * FROM datatype14;
CREATE INDEX index_id ON datatype14(username);
SHOW CREATE TABLE datatype14;SELECT * FROM student1;
SELECT name,avg(score) FROM student1 GROUP BY name;
SELECT name,avg(score),score,sum(score<60) as gk FROM student1 GROUP BY name HAVING gk>=2 ;
/**
select column from tab
(1)条件查询 where
a. 条件表达式的意义,表达式为真,则该行取出
b. 比较运算符= ,!=,< > <=>=
c. like , not like ('%'匹配任意多个字符,'_'匹配任意单个字符)in , not in , between and
d. is null , is not null
(2)分组 group by
一般要配合5个聚合函数使用:max,min,sum,avg,count
(3)筛选 having
(4)排序 order by
(5)限制 limit
*/
/*约束练习*/
#主键、非空、唯一、默认值,外键
CREATE TABLE IF NOT EXISTS key_test (
id TINYINT UNSIGNED PRIMARY KEY ,
username1 VARCHAR(30) NOT NULL,
username2 VARCHAR(30) UNIQUE KEY ,
username3 VARCHAR(30) DEFAULT 'lyzh'
)ENGINE =INNODB DEFAULT CHARACTER SET =UTF8;
CREATE TABLE IF NOT EXISTS key_test1 (
id TINYINT UNSIGNED,
username1 VARCHAR(30) ,
username2 VARCHAR(30) ,
username3 VARCHAR(30)
)ENGINE =INNODB DEFAULT CHARACTER SET =UTF8;
ALTER TABLE key_test1 ADD PRIMARY KEY (id);
ALTER TABLE key_test1 MODIFY COLUMN username1 VARCHAR(30) NOT NULL ;
ALTER TABLE key_test1 ADD UNIQUE KEY (username2);
ALTER TABLE key_test1 MODIFY COLUMN username3 VARCHAR(30) DEFAULT 'lyzh';
ALTER TABLE key_test1 ADD COLUMN username4 VARCHAR(30);
ALTER TABLE key_test ADD COLUMN id1 TINYINT UNSIGNED;
ALTER TABLE key_test1 ADD FOREIGN KEY (username4) REFERENCES key_test(id1);
ALTER TABLE key_test1 DROP PRIMARY KEY;
ALTER TABLE key_test1 DROP INDEX username2;
ALTER TABLE key_test1 DROP INDEX username2_2;
/*索引练习*/
CREATE TABLE IF NOT EXISTS index_test (
id INT ZEROFILL PRIMARY KEY ,
username1 VARCHAR(30) UNIQUE KEY ,
username2 VARCHAR(30) ,
INDEX index1 (username2)
);
SELECT * FROM student1;
SELECT name,max(score) FROM student1 GROUP BY name;
SHOW FULL COLUMNS FROM student1;
SHOW DATABASES ;
SHOW TABLES ;
SHOW PROCEDURE STATUS ;
SELECT view3;
SHOW TRIGGERS ;
SHOW FUNCTION STATUS ;
/*子查询:where型子查询,form型子查询*/
#where
SELECT * FROM datatype14 WHERE id= 12;
#form
SELECT d14.* FROM (SELECT * FROM datatype14) as d14;
#exists 左边必须和右边关联一下,然后看右边是否存在
SELECT * FROM datatype14 d WHERE exists(SELECT * FROM datatype14 WHERE d.id = id and id>105);
#delete all
TRUNCATE datatype14;
#union 两边的字段必须要一致,完全相当的列将要进行合并,每一行列都是要比较的。所以比较耗时
#union all :union耗时的解决办法就是使用union all,完全进行合并,不进行比较
/***************************高级篇**********************************/
/*触发器 触发器的操作和目标,操作用new和old代替此条数据来作为目标的引用*/
CREATE TABLE IF NOT EXISTS tr1 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age TINYINT
);
CREATE TABLE IF NOT EXISTS tr2 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age TINYINT
);
CREATE TRIGGER tr1 BEFORE INSERT ON tr1
FOR EACH ROW
BEGIN
INSERT INTO tr2 VALUE (DEFAULT ,'lyzh1',27);
END;
SELECT * FROM tr1;
SELECT * FROM tr2;
INSERT INTO tr1 VALUE (DEFAULT ,'lyzh1',27);
SHOW CREATE TRIGGER tr1;
DROP TRIGGER tr1;
DROP TRIGGER tr2;
CREATE TRIGGER tr2 AFTER INSERT ON tr1
FOR EACH ROW
BEGIN
#UPDATE tr2 SET age = new.age WHERE id = new.id;
INSERT INTO tr2 VALUE (DEFAULT ,new.name,new.age);
END;
INSERT INTO tr1 VALUE (DEFAULT ,'lyzh5',29);
CREATE TRIGGER tr1 BEFORE INSERT ON tab1
FOR EACH ROW
BEGIN
DECLARE rum INT;
SELECT id INTO rum FROM tab1;
IF rum>0 THEN
#SELECT rum;
SET rum = 20;
END IF;
END;
#FOR EACH ROW:每一行受影响,触发器都执行,叫做行级触发器
#在oracle中,如果不写FOR EACH ROW,那么无论影响了多少行,触发器都只执行一行。
/*存储过程*/
CREATE PROCEDURE p1()
BEGIN
SELECT 2+3 FROM dual;
END;
CALL p1();
SHOW PROCEDURE STATUS ;
SHOW CREATE PROCEDURE p1;
SHOW CREATE PROCEDURE zhilubaby_v_2.getLookupStr;
CREATE PROCEDURE p2()
BEGIN
DECLARE num TINYINT UNSIGNED DEFAULT 1;
SET num:=num+1;
END;
DROP PROCEDURE IF EXISTS ms1.p3;
CREATE PROCEDURE p3()
BEGIN
DECLARE num INT DEFAULT 20;
WHILE num>0 DO
SELECT num FROM dual;
SET num:=num-1;
END WHILE;
END;
CALL p3();
DROP PROCEDURE cur2;
/*游标*/
CREATE PROCEDURE cur2(OUT res VARCHAR(1000))
BEGIN
DECLARE down INT DEFAULT 0;
DECLARE id INT;
DECLARE username VARCHAR(50);
DECLARE birth TIMESTAMP;
DECLARE cur1 CURSOR FOR SELECT id,username,birth FROM tab1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET down = 1;
DECLARE result VARCHAR(1000);
OPEN cur1;
REPEAT
FETCH cur1 INTO id,username,birth;
IF NOT down THEN
SET res:= username;
END IF;
UNTIL downEND REPEAT;
CLOSE cur1;
END;
/*游标*/
CREATE PROCEDURE c1()
BEGIN
SELECT * FROM prov;
END;
CALL c1();
#自定义循环次数
DROP PROCEDURE IF EXISTS c2;
CREATE PROCEDURE c2(OUT res VARCHAR(2000))
BEGIN
#定义所有行数为循环次数
DECLARE cnt INT DEFAULT 0;
DECLARE row_id INT;
DECLARE row_name VARCHAR(50);
DECLARE result VARCHAR(2000) DEFAULT '';
DECLARE i INT DEFAULT 0;
DECLARE cursor1 CURSOR FOR SELECT id,name FROM prov;
SELECT count(id) INTO cnt FROM prov;
OPEN cursor1;
REPEAT
SET i:=i+1;
FETCH cursor1 INTO row_id,row_name;
SET result := concat(concat(result,row_name),',');
UNTIL i>=cnt END REPEAT;
CLOSE cursor1;
SET res:=result;
SELECT 'c2-----------------------------';
END;
SET @res2 = '';
CALL c2(@res2);
SELECT @res2;
#利用continue的这种方法达不到效果,会多取一次
DROP PROCEDURE IF EXISTS c3;
CREATE PROCEDURE c3(OUT res VARCHAR(2000))
BEGIN
DECLARE row_id INT;
DECLARE row_name VARCHAR(50);
DECLARE result VARCHAR(2000) DEFAULT '';
DECLARE you TINYINT DEFAULT 1;
DECLARE cursor1 CURSOR FOR SELECT id,name FROM prov;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET you:=0;
OPEN cursor1;
REPEAT
FETCH cursor1 INTO row_id,row_name;
SET result:=concat(concat(result,row_name),',');
UNTIL you=0 END REPEAT;
CLOSE cursor1;
SET res:=result;
SELECT 'c3-----------------------------';
END;
SET @res3 = '';
CALL c3(@res3);
SELECT @res3;
#利用exist来代替continue,好像也不行,反正没试出来
DROP PROCEDURE IF EXISTS c4;
CREATE PROCEDURE c4(OUT res VARCHAR(2000))
BEGIN
DECLARE row_id INT;
DECLARE row_name VARCHAR(50);
DECLARE result1 VARCHAR(2000) DEFAULT '';
DECLARE result VARCHAR(2000) DEFAULT '';
DECLARE you INT DEFAULT 1;
DECLARE cursor1 CURSOR FOR SELECT id,name FROM prov;
DECLARE EXIT HANDLER FOR NOT FOUND SET you:=0;
OPEN cursor1;
REPEAT
FETCH cursor1 INTO row_id,row_name;
SET result1:=concat(concat(result1,row_name),',');
SELECT result1;
SET result1:= result;
UNTIL you=0 END REPEAT;
CLOSE cursor1;
SELECT result1;
SET res:=result1;
SELECT 'c4-----------------------------';
END;
SET @res4 = '';
CALL c4(@res4);
SELECT @res4;
#利用 SQLSTATE
DROP PROCEDURE IF EXISTS c5;
CREATE PROCEDURE c5(OUT res VARCHAR(2000))
BEGIN
DECLARE down INT DEFAULT 0;
DECLARE row_id INT;
DECLARE row_name VARCHAR(50);
DECLARE result VARCHAR(2000) DEFAULT '';
DECLARE cursor1 CURSOR FOR SELECT id,name FROM prov;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET down := 1;
OPEN cursor1;
FETCH cursor1 INTO row_id,row_name;
REPEAT
SET result:=concat(concat(result,row_name),',');
FETCH cursor1 INTO row_id,row_name;
UNTIL down END REPEAT;
CLOSE cursor1;
SET res:=result;
SELECT 'c5-----------------------------';
END;
SET @res5 = '';
CALL c5(@res5);
SELECT @res5;
#自定义循环次数
DROP PROCEDURE IF EXISTS c6;
CREATE PROCEDURE c6(OUT res VARCHAR(2000))
BEGIN
#定义所有行数为循环次数
DECLARE cnt INT DEFAULT 0;
DECLARE row_id INT;
DECLARE row_name VARCHAR(50);
DECLARE result VARCHAR(2000) DEFAULT '';
DECLARE i INT DEFAULT 0;
DECLARE cursor1 CURSOR FOR SELECT id,name FROM prov;
SELECT count(id) INTO cnt FROM prov;
OPEN cursor1;
WHILE i SET i:=i+1;
FETCH cursor1 INTO row_id,row_name;
SET result := concat(concat(result,row_name),',');
END WHILE;
CLOSE cursor1;
SET res:=result;
SELECT 'c6-----------------------------';
END;
SET @res6 = '';
CALL c6(@res6);
SELECT @res6;
DROP PROCEDURE IF EXISTS c7;
CREATE PROCEDURE c7(OUT res VARCHAR(2000))
BEGIN
DECLARE result VARCHAR(2000) DEFAULT '112233';
SET res:=result;
SELECT 'c7-----------------------------';
END;
SET @res7 = '111';
CALL c7(@res7);
SELECT @res7;
#mysql主从复制两种方式
# 1、第一种基于全局事物的标识,每一台都有一个GTID全局事务标示符
# 2、第二种基于日志 bin-log(用的最多,用的最广)
#配置主从复制,要保持主从一直,主库可能会先有一些数据。所以要先锁住主库表,然后导出数据。
#然后配置主从,然后把主库备份的数据导入到从库中,就可以保持一致了。
/*主从复制 ceplication*/
#主 master ,产生binlog
#从 slave ,读取主的binlog产生relaylog,再把relaylog操作在自己的库表中
/**
1、主服务器配置binlog
2、从服务器配置relaylog
3、从服务器如何有权读取主master的binlog?授权,master要授权slave帐号
4、从服务器用帐号连接master
*/
SELECT * FROM datatype14;
SHOW VARIABLES ;
CREATE DATABASE if NOT EXISTS dt1 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;
SHOW DATABASES ;
SHOW CREATE DATABASE dt1;
/*
[root@localhost ~]# mysql -h172.16.53.134 -uroot -p
Enter password:
Welcome to the MySQL monitor.Commands end with ; or /g.
Your MySQL connection id is 2
Server version: 5.6.32-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.
mysql> select user,host,password from mysql.user;
+-------+-----------------------+-------------------------------------------+
| user| host| password|
+-------+-----------------------+-------------------------------------------+
| root| % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root| localhost.localdomain | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root| 127.0.0.1| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root| ::1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|| localhost| |
|| localhost.localdomain | |
| mysql | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------+-----------------------+-------------------------------------------+
7 rows in set (0.03 sec)
mysql> delete from mysql.user where user='root' and host='localhost.localdomain';
Query OK, 1 row affected (0.02 sec)
mysql> delete from mysql.user where user='root' and host='::1';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user where user='root' and host='127.0.0.1';
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> delete from mysql.user wherehost='localhost';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user wherehost='localhost.localdomain';
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
*/
#先给主服务器授权
GRANT REPLICATION CLIENT ,REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES ;
SHOW MASTER STATUS ;
#从
CHANGE MASTER TO
MASTER_HOST ='172.16.53.131',
MASTER_USER ='repl',
MASTER_PASSWORD ='123456',
MASTER_LOG_FILE ='mysql-bin.000003',
MASTER_LOG_POS = 2089;
SHOW SLAVE STATUS ;
SELECT * FROM mysql.user;
#创建测试主从复制表
CREATE TABLE IF NOT EXISTS repl (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
)ENGINE =INNODB AUTO_INCREMENT=1 DEFAULT CHARACTER SET =UTF8;
#插入主从复制数据
INSERT INTO repl VALUES (DEFAULT ,'lyzh1'),(DEFAULT ,'lyzh2'),(DEFAULT ,'lyzh3'),(DEFAULT ,'lyzh4');
SELECT * FROM repl;
DROP TABLE IF EXISTS card;
CREATE TABLE IF NOT EXISTS `card` (
id CHAR(32) NOT NULL COMMENT '帖子ID',
title VARCHAR(200) NOT NULL COMMENT '帖子标题',
frontimg VARCHAR(100) NOT NULL COMMENT '封面图片地址',
introduction VARCHAR(3000) NOT NULL COMMENT '简介',
content LONGTEXT COMMENT '帖子内容',
createtime TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
sort TINYINT NOT NULL COMMENT '排列顺序',
userid CHAR(32) NOT NULL COMMENT '发帖人',
pId CHAR(32) NOT NULL COMMENT '帖子所属(10001:职场经验,10002:面试技巧,10003:办公技能,10004:励志天地)',
PRIMARY KEY (id) COMMENT '主键索引',
INDEX (title) COMMENT '普通索引,title允许重复'
)ENGINE =INNODB CHARSET =UTF8;
INSERT INTO card(id, title, frontimg, introduction, content, sort, userid, pId) VALUES ("12345678912345678912345678912341","职场上的人际关系1","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912345678912345678912342","职场上的人际关系2","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912345678912345678912343","职场上的人际关系3","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912345678912345678912344","职场上的人际关系4","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912345678912345678912345","职场上的人际关系5","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912345678912345678912346","职场上的人际关系6","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912345678912345678912347","职场上的人际关系7","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912345678912345678912348","职场上的人际关系8","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("123456789123456121291232","职场上的人际关系1","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912545234567891232","职场上的人际关系2","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567891234567891234567891234","职场上的人际关系3","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567891234567891234567891235","职场上的人际关系4","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567891234567891234567891236","职场上的人际关系5","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567891234567891234567891237","职场上的人际关系6","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567891234567891234567891238","职场上的人际关系7","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567891234567891234567891239","职场上的人际关系8","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912345678912315678912341","职场上的人际关系1","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912345678912325678912342","职场上的人际关系2","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912345678912335678912343","职场上的人际关系3","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567891234567891233378912344","职场上的人际关系4","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("123456789123453545678912345","职场上的人际关系5","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567891234578845678912346","职场上的人际关系6","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567891234578862345678912347","职场上的人际关系7","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1456789123456876782345678912348","职场上的人际关系8","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001");,("12345678785678912335678912343","职场上的人际关系3","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678789367891233378912344","职场上的人际关系4","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678977875678912345","职场上的人际关系5","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912885678912346","职场上的人际关系6","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678999862345678912347","职场上的人际关系7","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567666876782345678912348","职场上的人际关系8","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567897775678912335678912343","职场上的人际关系3","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("123456789123435433378912344","职场上的人际关系4","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("12345678912325478912345","职场上的人际关系5","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("123456789128688845678912346","职场上的人际关系6","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("123456789122428862345678912347","职场上的人际关系7","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001"),("1234567891234568763585678912348","职场上的人际关系8","img/aaa.jpg","过度过程越短,越容易与企业融合","我内容你大爷了个拳头",1,"D3A9111F7B154F409FBC8232279BA74D","10001");
SELECT * FROM card;
SELECT count(*) FROM card;
CREATE TABLE IF NOT EXISTS databean(
data_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
username VARCHAR(50) NOT NULL COMMENT '用户名',
age TINYINT UNSIGNED NOT NULL COMMENT '年龄',
salary FLOAT UNSIGNED NOT NULL COMMENT '薪水',
create_time TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
PRIMARY KEY (data_id)
)ENGINE =INNODB AUTO_INCREMENT=1000 CHARSET ='UTF8';
INSERT INTO databean (username, age, salary) VALUE ('lyzh1', 25, 14000.1);
INSERT INTO databean (username, age, salary) VALUE ('lyzh2', 25, 14000.1);
INSERT INTO databean (username, age, salary) VALUE ('lyzh3', 25, 14000.1);
INSERT INTO databean (username, age, salary) VALUE ('lyzh4', 25, 14000.1);
INSERT INTO databean (username, age, salary) VALUE ('lyzh5', 25, 14000.1);
INSERT INTO databean (username, age, salary) VALUE ('lyzh6', 25, 14000.1);
INSERT INTO databean (username, age, salary) VALUE ('lyzh7', 25, 14000.1);
INSERT INTO databean (username, age, salary) VALUE ('lyzh8', 25, 14000.1);
INSERT INTO databean (username, age, salary) VALUE ('lyzh9', 25, 14000.1);
INSERT INTO databean (username, age, salary) VALUE ('lyzh10', 25, 14000.1);
INSERT INTO databean (username, age, salary) VALUE ('lyzh111', 25, 14000.1);
SELECT * FROM databean;/*优化系列*/#查看连接队列信息
SHOW PROCESSLIST ;
#创建2000万级别数据的数据表
DROP TABLE IF EXISTS person;
CREATE TABLE IF NOT EXISTS person (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
salary FLOAT(10,2)
)ENGINE =INNODB AUTO_INCREMENT=0 DEFAULT CHARACTER SET =UTF8;
#模拟数据信息 2000万
DROP PROCEDURE IF EXISTS insert_persion;
CREATE PROCEDURE insert_persion()
BEGIN
DECLARE size INT DEFAULT 20000000;
DECLARE i INT DEFAULT 0;
WHILE i>=0 AND i<= size DO
SET i:=i+1;
INSERT INTO person VALUE (DEFAULT ,concat('lyzh',i),i/10000);
END WHILE;
END;
CALL insert_persion();
SELECT count(0) FROM person;
EXPLAIN SELECT * FROM person LIMIT 0,100;
#查看执行信息
SHOW PROFILES ;
SHOW PROCESSLIST ;
SHOW PROFILES ;
#tmp_table_size| 16777216
ALTER TABLE person ADD UNIQUE PRIMARY KEY index_id(id);
/************************优化篇********************************/
#awk 用法
# mysqladmin -uroot -p123456 ext|awk '/Queries/{printf("%s/n",$0)}'
# mysqladmin -uroot -p123456 ext|awk '/Queries/{printf("%s/n",$0)}/Threads_connected/{printf("%s/n",$0)}/Threads_running/{printf("%s/n",$0)}'DROP TABLE IF EXISTS partition1;
CREATE TABLE IF NOT EXISTS partition1(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
brith DATE
)ENGINE =INNODB AUTO_INCREMENT=1 PARTITION BY HASH (month(brith)) PARTITIONS 6 ;CREATE TABLE aa(
id TINYINT UNSIGNED,
s_count FLOAT(10,2)
)ENGINE = INNODB PARTITION BY RANGE(s_count)(
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p1 VALUES LESS THAN (2000)
);SELECT count(*) FROM emp;

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台