mysql基础语法_杨小明 _mysql

建库建表语法，字段数据类型建库
IF NOT EXIutf8;
建表
TABLE (字段);
CREATE DATABASE IF NOT EXISTSusername DEFAULT CHARSET utf8;USE zuoye;CREATE TABLE student(st_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,st_name VARCHAR(20) NOT NULL,st_age DATE NOT NULL,st_xuehao VARCHAR(50),st_banji INT);
增删改查语法增
into (字段名)(要增加的数据)
INSERT INTO student(st_name,st_age,st_xueli,st_banji)VALUES("张一","2000-1-1","专科","1001"),("张二","2000-1-1","专科","1001"),("张三","2000-1-1","专科","1001");
删
from 表名 where 要删除的数据信息；
DELETE FROM student WHERE st_name="张三";
改
表名 set 要修改的字段数据 where 条件
update student set st_xueli="本科" where st_name="张三";
查
查询表数据： * from ;
表关联查询
CREATE DATABASE IF NOT EXISTS web DEFAULT CHARSET utf8;USE web;CREATE TABLE IF NOT EXISTS websites(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(50) NOT NULL,url VARCHAR(50) NOT NULL,alexa INT NOT NULL COMMENT "网站排名",country VARCHAR(20) NOT NULL);INSERT INTO websites(NAME,url,alexa,country)VALUES("googie","http://www.goole.com",1,"usa"),("淘宝","http://www.taobao.com",13,"cn"),("菜鸟","http://www.runoob.com",4689,"cn"),("微博","http://www.weibo.com",20,"cn"),("facebook","http://www.facebook.com",3,"usa"),("stachkovflow","http://www.stachkovflow.com",0,"ind"),("百度","http://www.baidu.com",4,"cn");CREATE TABLE IF NOT EXISTS access_log(aid INT PRIMARY KEY AUTO_INCREMENT,site_id INT NOT NULL,COUNT INT DEFAULT 0,DATE DATE);INSERT INTO access_log(site_id,COUNT,DATE)VALUES(1,45,"2016-05-10"),(2,100,"2016-05-10"),(5,415,"2016-05-10"),(5,200,"2016-05-10"),(6,7,"2016-05-10"),(7,145,"2016-05-10"),(8,45,"2016-05-10"),(9,545,"2016-05-10"),(10,201,"2016-05-10");USE web;#内连接,...inner join...onSELECT * FROM websites w INNER JOIN access_log a ON w.id=a.site_id;SELECT * FROM websites w,access_log a WHERE w.id=a.site_id;#左连接, left join...onSELECT * FROM websites w LEFT JOIN access_log a ON w.id=a.site_id;#右连接, right join..onSELECT * FROM websites w RIGHT JOIN access_log a ON w.id=a.site_id;#全连接=左连接 union 右连接SELECT * FROM websites w LEFT JOIN access_log a ON w.id=a.site_idUNION SELECT * FROM websites w RIGHT JOIN access_log a ON w.id=a.site_id;
子查询
查询部门编号大于2，且工资大于5000 的员工姓名、工资、部门编号、部门名称。
MySQL 索引
1.什么是索引？
一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。
2.为什么要有索引呢？
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
CREATE TABLE IF NOT EXISTS emp(emp_id INT PRIMARY KEY AUTO_INCREMENT,emp_name VARCHAR(20),salary INT,dept_id INT,manager_id INT );
普通索引
单独创建索引
CREATE INDEX index_name ON emp(emp_name);SELECT *FROM emp WHERE emp_name ="富贵";
修改表结构的方式添加索引
ALTER TABLE emp ADD INDEX index_salary (salary);
创建表的时候同时创建索引
CREATE TABLE IF NOT EXISTSarticle(id INT NULL AUTO_INCREMENT,title VARCHAR(30),content VARCHAR(50),dt DATE,PRIMARY KEY(id),INDEX index_title (title));
删除索引
DROP INDEX index_name ON emp;
唯一索引
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须是唯一的，创建方法和普通索引类似。
创建唯一索引
CREATE UNIQUE INDEX index_name ON emp(emp_name);
组合索引
语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引
CREATE INDEX index_ct ON article(content,dt);SELECT * FROM article WHERE content="123" AND dt="2022-1--10";
MySQL视图
什么是视图？
视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个语句保存在数据字典中的。
视图是干什么用的？
通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。基表：用来创建视图的表叫做基表
为什么要使用视图？
（1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
（2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
【mysql基础语法_杨小明】（3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。
创建视图
CREATE VIEW v_author(编号,姓名)ASSELECT * FROM author WITH CHECK OPTION;
视图的更改
orviewas 语句;
在视图存在的情况下可对视图进行修改，视图不在的情况下可创建视图
案例
CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;USE info;#创建作者表CREATE TABLE IF NOT EXISTS author(id INT NOT NULL AUTO_INCREMENT,author_name VARCHAR(50) DEFAULT NULL,PRIMARY KEY(id));INSERT INTO author(author_name)VALUES('naamman'),('lucy'),('lily'),('jack');#创建博客表CREATE TABLE IF NOT EXISTS blog(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(50) DEFAULT NULL,content VARCHAR(100) DEFAULT NULL,author_id INT DEFAULT NULL);INSERT INTO blog(title,content,author_id)VALUES('测试博客01','博客内容01',1),('测试博客02','博客内容02',2),('测试博客03','博客内容03',3),('测试博客04','博客内容04',4);#创建视图:逻辑上的虚拟表CREATE VIEW v_author(编号,姓名)ASSELECT * FROM author WITH CHECK OPTION;#查询视图SELECT * FROM v_author;#创建多表视图(自己写)CREATE VIEW v_a_b(作者名字,博客标题,博客内容)ASSELECT a.author_name,b.title,b.content FROM author a LEFT JOIN blog b ON a.id=b.author_id; SELECT * FROM v_a_b;#修改视图:没有就创建,有就替换CREATE OR REPLACE VIEW v_blog(编号,标题,内容,作者编号) AS SELECT * FROM blogWITH CHECK OPTION;#修改视图的数据->修改基表数据UPDATE v_blog SET 内容 = '修改后的内容' WHERE 编号 = 1;#部分数据创建视图CREATE OR REPLACE VIEW v_blog_1(编号,标题,内容,作者编号)AS SELECT * FROM blog WHERE author_id=1WITH CHECK OPTION;#with check option:where条件约束INSERT INTO v_blog_1(编号,标题,内容,作者编号)VALUES(10,'123','123',1);
Mysql事务
原子性：一个事务（）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read ）、读提交（read ）、可重复读（ read）和串行化（）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
事务控制语句：
BEGIN 或 START显式地开启一个事务；
也可以使用WORK，不过二者是等价的。会提交事务，并使已对数据库进行的所有修改成为永久性的；

文章插图
也可以使用WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
，允许在事务中创建一个保存点，一个事务中可以有多个；
删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
TO把事务回滚到标记点；
SET用来设置事务的隔离级别。存储引擎提供事务的隔离级别有READ 、READ 、 READ 和。
MYSQL 事务处理主要有两种方法
1、用 BEGIN, , 来实现
BEGIN 开始一个事务
事务回滚
事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET =0 禁止自动提交
SET =1 开启自动提交
案例

USE test;#创建表CREATE TABLE runoob(id INT(5));SELECT * FROM runoob;#开启事务BEGIN;#插入数据INSERT INTO runoob (id)VALUES(5);INSERT INTO runoob (id)VALUES(6);#提交事务COMMIT;#开启事务BEGIN;#插入数据INSERT INTO runoob (id)VALUES('aaa');INSERT INTO runoob (id)VALUES(6);#事务回滚ROLLBACK

Mysql触发器
触发器（）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（，，）时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。触发器创建语法四要素：
1.监视地点(table)
2.监视事件(//)
3.触发时间(after/)
4.触发事件(//)
触发器SQL语法：

create trigger triggerNameafter/before insert/update/delete on 表名for each row#这句话在mysql是固定的beginsql语句;end;

案例

CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;USE info;CREATE TABLE student(username VARCHAR(30) NOT NULL,PASSWORD VARCHAR(20) NOT NULL,shid INT AUTO_INCREMENT PRIMARY KEY,birthday DATE);INSERT INTO student(username,PASSWORD,birthday)VALUES("王二","111111","2016-08-23"),("李四","123456","2016-07-23"),("杨三","123456","2016-08-17"),("刘五","000000","2016-08-18"),("黄六","666666","2016-08-23");SELECT * FROM student;CREATE TABLE IF NOT EXISTS cj(NUMBER INT ,stu_id INT ,stu_name VARCHAR(20),math FLOAT,chinese FLOAT,english FLOAT	);#触发器CREATE TRIGGER ins_stuAFTER INSERT ON student FOR EACH ROWINSERT INTO cj(num,stu_id,stu_name,math,chinese,english)VALUES(1,new.shid,new.username,88,88,88);#执行触发过程INSERT INTO student(username,PASSWORD,birthday)VALUES('阿伟','222222','2016-12-21');

mysql查询优化
mysql的性能优化包罗甚广：索引优化，查询优化，查询缓存，服务器设置优化，操作系统和硬件优化，应用层面优化（web服务器，缓存）等等。这里的记录的优化技巧更适用于开发人员，都是从网络上收集和自己整理的，主要是查询语句上面的优化，其它层面的优化技巧在此不做记录。
mysql优化指标
执行时间检查的行数返回的行数
1. count的优化
比如：计算id大于5的城市 a.count(*) from world.city where id > 5; b.( count(*) from world.city) – count(*) from world.city where id 0) 2. SUM(T1.C1) FROM( * FROM T2 WHERE T2.C2=T1.C2) 两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用代替。如： IF ( COUNT(*) FROMWHERE= ‘xxx’)可以写成：IF( * FROMWHERE= ‘xxx’)
mysql总复习

三大数据类型：字符串：char、varchar 。数值：int、float 。日期：date、time、datetime、timestamp增：insert into 表名(字段名1，字段名2)values(值1，值2);删：delete from 表名 where 字段名=字段指定的值;改：update user set gender=”女” where id=4;查：基本查询：select * from 表名;查询字句：where：select * from u where id=1;排序：select * from u order by id;select * from u order by id desc;分组：select * from u group by id;去重：select distinct name form u;内连接：select * from web w inner join web2 w2 on w.id=w2.id;左连接：select * from web w left join web2 w2 on w.id=w2.id;右连接：select * from web w right join web2 w2 on w.id=w2.id;全连接：左连接 union 右连接select * from web w left join web2 w2 on w.id=w2.idunionselect * from web w right join web2 w2 on w.id=w2.id;三表关联查询：select st.*,sc.*,su.* from stu stinner join score sc on st.no=sc.noleft join sub su on sc.subno=su.subnowhere st.no>0; 子查询，单值select * from emp where salary > (select salary from emp where emp_name=”王强”);查询工资大于王强的数据子查询，多值select * from emp where dept_id = (select dept_id from emp where emp_name=”张建国”)and salary > (select salary from emp where emp_name = “孙岩”);子查询，中间表select * from (select * from emp where dept_id > 2 and salary > 5000) e;创建视图：逻辑上的虚拟表create view v_auther(编号,姓名)asselect * from autherwith check option;查询视图和查询表一样创建索引create index index_name on emp(emp_name); 在emp表的emp_name字段创建索引，索引名为index_name添加unique说明它的值不能重复单独创建唯一索引create unique index index_name on emp(emp_name);修改表创建唯一索引alter table article add unique index index_title(title);组合索引create index index_ct on article(content,dt);create table if not exists article(id int not null auto_increment,title varchar(30) unique,在列数据的后面添加unique说明它的值不能重复content varchar(50),dt date,primary key(id),unique index index_title(title), 创建表创建唯一索引index index_cd(content,dt) 创建表创建组合索引)auto_increment=0 default charset=utf8;1，2，3 ， 4，5，回滚操作执行代码的顺序开启事务#1begin;插入语句#2insert into runoob_transation_test(id)value(‘5’);insert into runoob_transation_test(id)value(6);提交事务 提交后不能回滚commit;回滚#4rollback;查询#3 #5select * from runoob_transation_test;#学生表create table student(username varchar(20),password varchar(20),stuid int primary key auto_increment,birthday date);insert into student(username,password,birthday)values('王二','111111','2016-08-23'),('李四','123456','2016-07-23'),('杨三','123456','2016-08-17'),('刘五','000000','2016-08-18'),('黄六','666666','2016-08-23');#成绩表create table if not exists cj(number int,stu_id int,stu_name varchar(20),math float,chinese float,english float);触发器1 将student表的stuid和username字段插入到cj表的相对应字段中(下同)create trigger ins_stuafter insert on student for each rowinsert into cj(number,stu_id,stu_name,math,chinese,english)values(1,new.stuid,new.username,88,77,99)触发器2delimiter $create trigger ins_stuafter insert on student for each rowbegininsert into cj(number,stu_id,stu_name,math,chinese,english)values(1,new.stuid,new.username,88,77,99);end $delimiter ;执行触发过程 这里的stuid和username的字段值会插入到cj表insert into student(username,password,birthday)values(‘张三’,’22222’,’2016-08-23’);创建存储过程 两张表会显示两个结果delimiter $create procedure testa()beginselect * from student;select * from cj;end $delimiter ;调用存储过程call testa();创建存储过程delimiter $create procedure test2()begin声明变量类型declare un varchar(32) default ‘’;给变量赋值set un=’xiaoxiao’;将student表的数据复制给un ， 复制的是student表username列的数据select username into un from student where stuid=3;#查询un变量，返回select un;end $delimiter ;1避免使用in或not in ， 因为是不连续的范围，不会使用索引 ， 可以使用between ... and ...select * from emp where age in(100,150,200);select * from emp where age between 100 and 200;2.尽量不使用select into,因为会导致表锁定,其他用户就不能使用此表3.未使用索引select * from T1 where nextprocess = 1 and processed in (8,32,45);强制使用索引select * from T1 force index(IX_ProcessID)where nextprocess = 1 and processed in (8,32,45);4. 模糊查询where like,母打头'l%'可以使用索引，非字母打头'%l%'不会使用索引不会使用索引 ， 会进行全表扫描的情况select * from T2 where name like ’%L%’;select * from T2 where substring(name,2,1)=’L’; -- 截取name从第二个字符开始截取一个字符会使用索引的情况select * from T1 where name like ‘L%’;5. 尽量不要修改主键字段6. 当修改varchar型字段时，尽量使用相同长度内容的值代替7. 尽量最小化对于含有update触发器的表的update操作8. 避免update将要复制到其他数据库的列9. 避免update建有很多索引的列修改值索引就要重新排序 尽量不修改带有索引的字段10.避免update在where字句条件中的列例如：update a set name='n' where name = 'a'