MySQL语句

CREATE TABLE `employee`(
  `id` INT UNSIGNED AUTO_INCREMENT,
  `name` VARCHAR(25),
  `gender` BIT(1),
  `age` TINYINT,
  `department` VARCHAR(20),
  `salary` DOUBLE(7, 2),
  PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO emp(name, salary, depart) values ("瞎驴", 30000, "python"),
("青神", 8000, "ui"),
("Mr.Clean", 50000, "技术研发部");

INSERT INTO emp values (20, 22, "小雨", 1, 9000, "人力资源", 1);

INSERT INTO emp set name = "珊珊";

UPDATE emp set salary = salary + 20000 where name = "lupe";

UPDATE emp set salary = salary + 2000, depart = "安保部" where name = "瞎驴";

DELETE FROM emp where name = "瞎驴" OR id = 20;

DELETE FROM emp;

CREATE TABLE `ExamResult`(
  `id` INT AUTO_INCREMENT,
  `name` VARCHAR(20),
  `JS` DOUBLE,
  `Django` DOUBLE,
  `Database` DOUBLE,
  PRIMARY KEY ( `id` )
);

INSERT INTO ExamResult VALUES (1, 'xiaoyu', 60, 59, 62),
(2, 'xiaomei', 70, 83, 92),
(3, 'alex', 40, 63, 52),
(4, 'xiaoya', 90, 93, 97);

INSERT INTO examresult (name, js, css)
VALUES
("liuying", 45, 56);

CREATE TABLE examresult(
  id INT AUTO_INCREMENT,
  name VARCHAR(20),
  JS DOUBLE,
  Django DOUBLE,
  Python DOUBLE,
  PRIMARY KEY(id)
);

CREATE TABLE ClassCharger(
  id TINYINT PRIMARY  KEY auto_increment,
  name VARCHAR(20),
  age INT,
  is_marriged TINYINT
);

INSERT INTO ClassCharger (name, age, is_married) VALUES
  ("冰冰", 52, 0),
  ("丹丹", 34, 0),
  ("歪歪", 32, 0),
  ("珊珊", 28, 0),
  ("小雨", 61, 0);

CREATE TABLE Student2(
  id INT PRIMARY KEY auto_increment,
  name VARCHAR (20),
  charger_id TINYINT,
  FOREIGN KEY(charger_id) REFERENCES classcharger(id)
)

INSERT INTO Student2(name, charger_id) values ("alvin1", "2"),
  ("alvin2", "4"),
  ("alvin3", "1"),
  ("alvin4", "3"),
  ("alvin5", "1"),
  ("alvin6", "3"),
  ("alvin7", "2");

/* 添加外键 */
ALTER TABLE student ADD CONSTRAINT abc
  FOREIGN KEY(charger_id)
  REFERENCES classcharger(id);

/* 删除外键 */
ALTER TABLE student DROP FOREIGN KEY abc;

/* 级联删除 */
CREATE TABLE Student3(
  id INT PRIMARY KEY auto_increment,
  name VARCHAR (20),
  charger_id TINYINT,
  FOREIGN KEY (charger_id) REFERENCES classcharger(id) on DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO Student3(name, charger_id) VALUES ("alvin1", 2),
("alvin2", 2),
("alvin3", 4),
("alvin4", 5),
("alvin5", 3),
("alvin6", 5),
("alvin7", 2);

CREATE TABLE C(
  id TINYINT PRIMARY KEY auto_increment,
  name VARCHAR (20),
  age INT,
  is_married TINYINT
)

INSERT INTO C (name, age, is_married) VALUES
  ("BingBing", 22, 0),
  ("QingQing", 28, 0),
  ("WangRan", 29, 0),
  ("LuFei", 50, 0),
  ("ZhangHong", 33, 0);

CREATE TABLE S3(
  name VARCHAR (20),
  charger_id TINYINT,
  FOREIGN KEY (charger_id) REFERENCES C(id) ON DELETE CASCADE
)

INSERT INTO S3 (name, charger_id) VALUES
  ("ace1", 1),
  ("ace2", 2),
  ("ace3", 3),
  ("ace4", 4),
  ("ace5", 5),
  ("ace6", 3),
  ("ace7", 2);

-- set null
-- 删除外键
alter table S3 drop foreign key  s3_ibfk_1;
-- 添加外键
ALTER TABLE S3 ADD CONSTRAINT s3_fk_c FOREIGN KEY (charger_id) REFERENCES C(id) ON DELETE SET NULL;

-- 多表查询之连接查询
-- 连接查询
-- 内连接：inner join
-- 外连接：left join right join
-- 全连接：full join

CREATE TABLE tableA(id INT PRIMARY KEY, name VARCHAR(20));

CREATE TABLE tableB(id INT PRIMARY  KEY, name VARCHAR(20), tableA_id INT);

INSERT INTO tableA VALUES(1, "alvin");
INSERT INTO tableA VALUES(2, "xiayu");
INSERT INTO tableA VALUES(3, "zhang");

INSERT INTO tableB VALUES(1, "lile", 1);
INSERT INTO tableB VALUES(2, "wangqing", 2);
INSERT INTO tableB VALUES(3, "zhouqiang", 3);

-- 内连接查询
SELECT * FROM tableA, tableB WHERE tableA.id=tableB.tableA_id;

SELECT tableA.id, tableA.name, tableB.name FROM tableA, tableB WHERE tableA.id=tableB.tableA_id;

SELECT * FROM tableB INNER JOIN tableA ON tableB.tableA_id=tableA.id;

-- 准备两张表
-- company.employee
-- company.department

CREATE TABLE employee(
  emp_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
  emp_name VARCHAR (50),
  age INT,
  dept_id INT
);

INSERT INTO employee(emp_name, age, dept_id) VALUES
  ("A", 19, 200),
  ("B", 26, 201),
  ("C", 30, 201),
  ("D", 24, 202),
  ("E", 20, 200),
  ("F", 38, 204);

CREATE TABLE department(
  dept_id INT,
  dept_name VARCHAR(100)
);

INSERT INTO department VALUES
  (200, "人事部"),
  (201, "技术部"),
  (202, "销售部"),
  (203, "财政部");

SELECT employee.emp_name, department.dept_name FROM employee, department WHERE employee.dept_id=department.dept_id
  AND employee.emp_name="A";

SELECT employee.emp_name, department.dept_name FROM department INNER JOIN employee
  ON employee.dept_id=department.dept_id
  AND employee.emp_name="A";

-- 外连接
-- 左外连接
SELECT employee.emp_name, department.dept_name FROM department LEFT JOIN employee
  ON employee.dept_id=department.dept_id;

SELECT employee.emp_name, department.dept_name FROM employee LEFT JOIN department
  ON employee.dept_id=department.dept_id;

SELECT employee.emp_name, department.dept_name FROM employee LEFT JOIN department
  ON employee.dept_id=department.dept_id;

-- 间接实现全外连接
SELECT * FROM employee LEFT JOIN department ON employee.dept_id=department.dept_id
  UNION SELECT * FROM employee RIGHT JOIN department ON employee.dept_id=department.dept_id;

-- 多表查询之复合查询与子查询
SELECT employee.emp_name, department.dept_name FROM employee, department
  WHERE employee.dept_id=department.dept_id
  AND department.dept_name="jishubu";


-- 找出大于25岁员工所在部门
SELECT department.dept_name FROM employee, department
  WHERE employee.dept_id=department.dept_id
  AND employee.age > 25;

-- 去重
SELECT DISTINCT department.dept_name FROM employee, department
  WHERE employee.dept_id=department.dept_id
  AND employee.age > 25;

-- 多表查询之子查询
-- 内层查询语句的查询结果，可以为外层查询语句提供查询条件
-- 子查询中可以包含：IN、NOT、ANY、ALL、EXISTS和NOT EXISTS等关键字
-- 还可以包含比较运算符：=、!=、>、<等

-- 1.带IN关键字的子查询
-- 查询employee表，但dept_id必须在department表中出现过
SELECT * FROM employee
  WHERE dept_id IN
  (SELECT dept_id FROM department);

-- 复制一张表
CREATE TABLE AA (SELECT * FROM employee);

-- 2.带比较运算符的子查询
-- =、!=、>、 >=、 <、 <=、 <>
-- 查询员工年龄大于等于25岁的部门
SELECT dept_id, dept_name from department
  WHERE dept_id IN
  (SELECT DISTINCT dept_id from employee WHERE age > 25);

--  3.带EXISTS关键字的子查询
--  EXISTS关键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询记录。
--  而是返回一个真假值。True或False
--  当返回一个真假值，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
SELECT * FROM employee
  WHERE EXISTS
  (SELECT dept_name FROM department WHERE dept_id=203);
--   department表中存在dept_id=203, True

SELECT * FROM employee
  WHERE EXISTS
  (SELECT dept_name FROM department WHERE dept_id=205);
-- Empty set (0.00 sec)


-- MySql之索引
-- 索引简介
/* 索引在MySql中也叫作“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非
  常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
*/

/* 索引优化应该是对查询性能优化最有效的手段了。
  索引能够轻易将查询性能提高好几个量级。
  索引相当于字典的音序表，如果要查某个字，如果不适用音序表，则需要从几百页中逐页去查。
*/

-- 索引特点：创建与维护索引会消耗很多时间与磁盘空间，但查询速度大大提高！

CREATE TABLE test1(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  salary INT DEFAULT 1000
);

INSERT INTO test1 (name) VALUES
  ("111"),
  ("222"),
  ("333"),
  ("444");
ALTER TABLE test1 modify name VARCHAR (20) UNIQUE;

-- 索引语法
-- 创建表时
CREATE TABLE 表名(
  字段名1 数据类型 [完整性约束条件],
  字段名2 数据类型 [完整性约束条件],
  [UNIQUE | FULLTEXT | SPATTAL] INDEX | KEY
  [索引名] (字段名[(长度)] [ASC | DESC])
)

CREATE TABLE emp(
  id INT,
  name VARCHAR(20),
  INDEX index_name (name)
)

-- 添加用户
use mysql;

INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv)
  VALUES ('localhost', 'guest', PASSWORD('guest123'), 'Y', 'Y', 'Y');

-- 重新载入授权表
FLUSH PRIVILEGES;


SELECT host, user, password FROM user WHERE user = 'guest';

CREATE DATABASE webmap;

USE webmap;

CREATE TABLE location (
  id INT AUTO_INCREMENT,
  mobile VARCHAR (256) NULL,
  sno VARCHAR (256) NULL,
  cno VARCHAR (256) NULL,
  pdatetime TIMESTAMP NULL,
  lat VARCHAR (256) NULL,
  lng VARCHAR (26) NULL,
  cellid VARCHAR (45) NULL,
  lac VARCHAR (45) NULL,
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET = UTF8;


INSERT INTO location (mobile, sno, cno, pdatetime, lat, lng, cellid, lac) VALUES
  ("17602128911", "S2", "C2", "2019-03-18 9:00:00", "39.868795", "116.268116", "53951", "29547"),
  ("17602128911", "S2", "C2", "2019-03-18 10:00:00", "39.866795", "116.268116", "53951", "29547"),
  ("17602128911", "S2", "C2", "2019-03-18 11:00:00", "39.868795", "116.266116", "53951", "29547");

-- A.什么参数都不带，取所有车辆最新数据
String sql = "SELECT * FROM webmap.location x where pdatetime=(SELECT MAX(pdatetime) FROM webmap.location
 y WHERE x.mobile = y.mobile) ORDER BY pdatetime ASC"

-- B.只有手机号数据，取改手机号最近50条数据
String sql = "SELECT * FROM webmap.location y y.mobile = '" + mobileNo + "' ORDER BY y.pdatetime ASC limit 50"

SELECT * FROM webmap.location y where y.mobile="17602128911" ORDER BY y.pdatetime ASC limit 50;

-- C.有时间+手机号，取该手机号该时间内所有数据
SELECT * FROM webmap.location y WHERE y.mobile="17602128911" AND y.pdatetime >= "2019-03-18" and y.pdatetime <= "2019-03-18 10:00:00"
  ORDER BY y.pdatetime ASC;

-- 创建全文索引示例
CREATE TABLE emp3 (
  id INT,
  name VARCHAR (30),
  resume VARCHAR (50),
  FULLTEXT INDEX index_resume (resume)
);

-- 创建多列索引示例
CREATE TABLE emp4 (
  id INT,
  name VARCHAR (30),
  resume VARCHAR (50),
  INDEX index_name_resume (name, resume)
)

-- 索引测试实验
CREATE table t1 (id INT, name VARCHAR (20));
-- 存储过程
DELIMITER $$
CREATE PROCEDURE autoinsert2()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i < 5000) DO
INSERT INTO t1 VALUES (i, 'lupe');
SET i=i+1;
END WHILE;
END$$
delimiter;

-- 调用函数

call autoinesert()

-- 添加索引
-- CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATTAL] INDEX 索引名
  ON 表名  (字段名[(长度)] [ASC | DESC]);

-- ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 AND [UNIQUE | FULLTEXT | SPATIAL] INDEX
  索引名 (字段名[(长度)]) [ASC | DESC]);

-- 删除索引
语法:DROP INDEX 索引名 ON 表名