一起做网店网站,各大网站网址目录,莞城发布最新通告,wordpress a 锚点数据库简介数据库(DB)数据库(database#xff0c;DB)是指长期存储在计算机内的#xff0c;有组织#xff0c;可共享的数据的集合。数据库中的数据按一定的数学模型组织、描述和存储#xff0c;具有较小的冗余#xff0c;较高的数据独立性和易扩展性#xff0c;并可为各种…数据库简介数据库(DB)数据库(databaseDB)是指长期存储在计算机内的有组织可共享的数据的集合。数据库中的数据按一定的数学模型组织、描述和存储具有较小的冗余较高的数据独立性和易扩展性并可为各种用户共享。更多信息可以见百度数据库管理系统软件(DBMS)数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件用于建立、使用和维护数据库简称DBMS。它对数据库进行统一的管理和控制以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据数据库管理员也通过dbms进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立修改和询问数据库。数据库管理系统是数据库系统的核心是管理数据库的软件。数据库管理系统就是实现把用户意义下抽象的逻辑数据处理转换成为计算机中具体的物理数据处理的软件。有了数据库管理系统用户就可以在抽象意义下处理数据而不必顾及这些数据在计算机中的布局和物理位置。大部分DBMS提供数据定义语言DDL(Data Definition Language)和数据操作语言DML(Data Manipulation Language)供用户定义数据库的模式结构与权限约束实现对数据的追加、删除等操作。常见的数据库管理软件甲骨文的oracle,IBM的db2sql server, Access,Mysql(开源免费跨平台).数据库系统(DBS)数据库系统DBS(Data Base System简称DBS)通常由软件、数据库和数据管理员组成。其软件主要包括操作系统、各种宿主语言、实用程序以及数据库管理系统。数据库由数据库管理系统统一管理数据的插入、修改和检索均要通过数据库管理系统进行。数据管理员负责创建、监控和维护整个数据库使数据能被任何有权使用的人有效使用。MySQLMySQL管理安装Linux--yum -y install mariadb mariadb-server或者--yum -y install mysql mysql-serverWindows--http://dev.mysql.com/downloads/mysql/启动--service mysqld start #开启--chkconfig mysqld on #设置开机自启或者--systemctl start mariadb--systemctl enable mariadb查看--ps aux |grep mysqld #查看进程--netstat -an |grep 3306 #查看端口设置密码--mysqladmin -uroot password 123 #设置初始密码初始密码为空因此-p选项没有用--mysqladmin -u root -p123 password 1234 #修改root用户密码登录--mysql #本地登录默认用户root空密码用户为root127.0.0.1--mysql -uroot -p1234 #本地登录指定用户名和密码用户为root127.0.0.1--mysql -uroot -p1234 -h 192.168.31.95 #远程登录用户为root192.168.31.95MySQL常用命令-- --启动mysql服务与停止mysql服务命令-- --net start mysql--net stop mysql-- -- --登陆与退出命令-- --mysql h 服务器IP -P 端口号 -u 用户名 -p 密码 prompt 命令提示符 delimiter 指定分隔符--mysql h 127.0.0.1 -P 3306 -uroot -p123--quit------exit----\q;-- -- --\s; ------my.ini文件[mysql] default-character-setgbk [mysqld] character-set-servergbk-- --prompt 命令提示符(\D:当前日期 \d:当前数据库 \u:当前用户)-- --\T(开始日志) \t(结束日志)-- --show warnings;-- --help() ? \h-- --\G-- --select now();--select version();--select user;-- --\c 取消命令-- --delimiter 指定分隔符忘记密码?方法1启动mysql时跳过授权表[rootcontroller ~]# service mysqld stop[rootcontroller ~]# mysqld_safe --skip-grant-table [rootcontroller ~]# mysqlmysql select user,host,password from mysql.user;----------------------------------------------------------------------------| user | host | password |----------------------------------------------------------------------------| root | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF || root | localhost.localdomain | || root | 127.0.0.1 | || root | ::1 | || | localhost | || | localhost.localdomain | || root | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |----------------------------------------------------------------------------mysql update mysql.user set passwordpassword(123) where userroot and hostlocalhost;mysql flush privileges;mysql exit[rootcontroller ~]# service mysqld restart[rootcontroller ~]# mysql -uroot -p123方法2(删库):删除与权限相关的库mysql所有的授权信息都丢失主要用于测试数据库或者刚刚建库不久没有授权数据的情况(从删库到跑路)[rootcontroller ~]# rm -rf /var/lib/mysql/mysql[rootcontroller ~]# service mysqld restart[rootcontroller ~]# mysqlSQLsql简介及规范sql是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集是一种功能齐全的数据库语言。在使用它时只需要发出“做什么”的命令“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便已经成为了数据库操作的基础并且现在几乎所有的数据库均支持sql。1 在数据库系统中SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写表名库名小写2 SQL语句可单行或多行书写以“;”结尾。关键词不能跨多行或简写。3 用空格和缩进来提高语句的可读性。子句通常位于独立行便于编辑提高可读性。4 注释单行注释--多行注释/*......*/5sql语句可以折行操作6 DDL,DML和DCLDML(data manipulation language) 它们是SELECT、UPDATE、INSERT、DELETE就象它的名字一样这4条命令是用来对数据库里的数据进行操作的语言DDL(data definition language)DDL比DML要多主要的命令有CREATE、ALTER、DROP等DDL主要是用在定义或改变表(TABLE)的结构数据类型表之间的链接和约束等初始化工作上他们大多在建立表时使用。DCL(Data Control Language)是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句包括(grant,deny,revoke等)语句。在默认状态下只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL。MySQL数据类型MySQL支持多种类型大致可以分为三类数值、日期/时间和字符串(字符)类型。数值类型日期和时间类型表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个零值当指定不合法的值或MySQL不能表示的值时使用零值。字符串类型CHAR和VARCHAR类型类似但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。BINARY和VARBINARY类类似于CHAR和VARCHAR不同的是它们包含二进制字符串而不要非二进制字符串。也就是说它们包含字节字符串而不是字符字符串。BLOB是一个二进制大对象可以容纳可变数量的数据。有4种BLOB类型TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。有4种TEXT类型TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型有相同的最大长度和存储需求。数据库操作(DDL)--1.创建数据库(在磁盘上创建一个对应的文件夹)create database [if not exists] db_name [character set xxx]--2.查看数据库show databases;查看所有数据库showcreate database db_name; 查看数据库的创建方式--3.修改数据库alter database db_name [character set xxx]--4.删除数据库drop database [if exists] db_name;--5.使用数据库切换数据库 use db_name; --注意进入到某个数据库后没办法再退回之前状态但可以通过use进行切换查看当前使用的数据库 select database();数据表操作--1.创建表(类似于一个excel表)create tabletab_name(field1 type[完整性约束条件],field2 type,...fieldn type)[character set xxx];--创建一个员工表employeecreate tableemployee(idint primary keyauto_increment ,namevarchar(20),genderbit default 1, --gender char(1) default 1 ----- 或者 TINYINT(1)birthday date,entry_date date,jobvarchar(20),salarydouble(4,2) unsigned,resumetext --注意这里作为最后一个字段不加逗号);/*约束:primary key (非空且唯一) :能够唯一区分出当前记录的字段称为主键uniquenot nullauto_increment 主键字段必须是数字类型。外键约束 foreign key*/--2.查看表信息desctab_name 查看表结构show columnsfromtab_name 查看表结构show tables 查看当前数据库中的所有的表showcreate tabletab_name 查看当前数据库表建表语句--3.修改表结构--(1)增加列(字段)alter table tab_name add [column]列名 类型完整性约束条件firstafter 字段名;alter table user add addr varchar(20) not null unique first/after username;#添加多个字段alter tableusers2add addr varchar(20),add age intfirst,add birth varchar(20) after name;--(2)修改一列类型alter table tab_name modify 列名 类型 [完整性约束条件]firstafter 字段名;alter table users2 modify age tinyint default 20;alter table users2 modify age intafter id;--(3)修改列名alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件]firstafter 字段名;alter table users2 change age Age int default 28first;--(4)删除一列alter table tab_name drop [column]列名;--思考删除多列呢删一个填一个呢alter tableusers2add salary float(6,2) unsigned not nullafter name,dropaddr;--(5)修改表名rename table 表名 to新表名;--(6)修该表所用的字符集alter table student character setutf8;--4.删除表drop tabletab_name;---5 添加主键删除主键alter table tab_name add primary key(字段名称,...)alter table users drop primary key;eg:mysql create table test5(num intauto_increment);ERROR1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keycreate table test(num int primary keyauto_increment);--思考如何删除主键alter table test modify id int; --auto_increment没了但这样写主键依然存在所以还要加上下面这句alter table test drop primary key;--仅仅用这句也无法直接删除主键--唯一索引alter table tab_name add unique [index|key] [索引名称](字段名称,...)alter table users add unique(name)--索引值默认为字段名show create table users;alter table users add unique key user_name(name);--索引值为user_name--添加联合索引alter table users add unique index name_age(name,age);#show create tableusers;--删除唯一索引alter table tab_name drop {index|key} index_name表记录操作(增删改)--1.增加一条记录insert/*insert into tab_name (field1,filed2,.......) values (value1,value2,.......);*/create tableemployee_new(idint primary keyauto_increment,namevarchar(20) not null unique,birthdayvarchar(20),salaryfloat(7,2));insert into employee_new (id,name,birthday,salary) values(1,yuan,1990-09-09,9000);insert into employee_new values(2,alex,1989-08-08,3000);insert into employee_new (name,salary) values(xialv,1000);--插入多条数据insert into employee_new values(4,alvin1,1993-04-20,3000),(5,alvin2,1995-05-12,5000);--set插入: insert into tab_name set 字段名值insert into employee_new set id12,namealvin3;--2.修改表记录 update tab_name set field1value1,field2value2,......[where 语句]/*UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如没有WHERE子句则更新所有的行。*/update employee_new set birthday1989-10-24 WHERE id1;--- 将yuan的薪水在原有基础上增加1000元。update employee_new set salarysalary4000 where nameyuan;--3.删除表纪录delete from tab_name [where ....]/*如果不跟where语句则删除整张表中的数据delete只能用来删除一行记录delete语句只能删除表中的内容不能删除表本身想要删除表用dropTRUNCATE TABLE也可以删除表中的所有数据词语句首先摧毁表再新建表。此种方式删除的数据不能在事务中恢复。*/--删除表中名称为’alex’的记录。delete from employee_new where namealex;--删除表中所有记录。delete from employee_new;--注意auto_increment没有被重置:alter table employee auto_increment1;--使用truncate删除表中记录。truncate table emp_new;表记录操作(单表查询)--查询表达式SELECT *|field1,filed2 ... FROMtab_nameWHERE条件GROUP BYfieldHAVING筛选ORDER BYfieldLIMIT 限制条数---准备表CREATE TABLEExamResult(idINT PRIMARY KEYauto_increment,nameVARCHAR (20),JSDOUBLE,DjangoDOUBLE,OpenStackDOUBLE);INSERT INTO ExamResult VALUES (1,yuan,98,98,98),(2,xialv,35,98,67),(3,alex,59,59,62),(4,wusir,88,89,82),(5,alvin,88,98,67),(6,yuan,86,100,55);--(1)select [distinct] *|field1field2...... from tab_name--其中from指定从哪张表筛选*表示查找所有列也可以指定一个列--表明确指定要查找的列distinct用来剔除重复行。--查询表中所有学生的信息。select * fromExamResult;--查询表中所有学生的姓名和对应的英语成绩。select name,JS fromExamResult;--过滤表中重复数据。select distinct JS ,name fromExamResult;--(2)select 也可以使用表达式并且可以使用: 字段 as 别名或者:字段 别名--在所有学生分数上加10分特长分显示。select name,JS10,Django10,OpenStack10 fromExamResult;--统计每个学生的总分。select name,JSDjangoOpenStack fromExamResult;--使用别名表示学生总分。select name as 姓名,JSDjangoOpenStack as 总成绩 fromExamResult;select name,JSDjangoOpenStack 总成绩 fromExamResult;select name JS from ExamResult; --what will happen?----记得加逗号--(3)使用where子句进行过滤查询。--查询姓名为XXX的学生成绩select * from ExamResult where nameyuan;--查询英语成绩大于90分的同学select id,name,JS from ExamResult where JS90;--查询总分大于200分的所有同学select name,JSDjangoOpenStack as 总成绩 fromExamResultwhere JSDjangoOpenStack200;--where字句中可以使用--比较运算符 !between 80 and 100值在10到20之间in(80,90,100) 值是10或20或30like yuan%/*pattern可以是%或者_如果是%则表示任意多字符此例如唐僧,唐国强如果是_则表示一个字符唐_只有唐僧符合。两个_则表示两个字符__*/--逻辑运算符在多个条件直接可以使用逻辑运算符 and or not--练习--查询JS分数在 70100之间的同学。select name ,JS from ExamResult where JS between 80 and 100;--查询Django分数为75,76,77的同学。select name ,Django from ExamResult where Django in (75,98,77);--查询所有姓王的学生成绩。select * from ExamResult where name like 王%;--查询JS分90Django分90的同学。select id,name from ExamResult where JS90 and Django 90;--查找缺考数学的学生的姓名select name from ExamResult where Database is null;--(4)Order by 指定排序的列排序的列即可是表中的列名也可以是select 语句后指定的别名。--select *|field1,field2... from tab_name order by field [Asc|Desc]--Asc 升序、Desc 降序其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。--练习--对JS成绩排序后输出。select * from ExamResult order byJS;--对总分排序按从高到低的顺序输出select name ,(ifnull(JS,0)ifnull(Django,0)ifnull(Database,0))总成绩from ExamResult order by 总成绩 desc;--对姓李的学生成绩排序输出select name ,(ifnull(JS,0)ifnull(Django,0)ifnull(OpenStack,0))总成绩from ExamResult where name like a%order by 总成绩 desc;--(5)group by 分组查询CREATE TABLEorder_menu(idINT PRIMARY KEYauto_increment,product_nameVARCHAR (20),priceFLOAT(6,2),born_date DATE,classVARCHAR (20));INSERT INTO order_menu (product_name,price,born_date,class) VALUES(苹果,20,20170612,水果),(香蕉,80,20170602,水果),(水壶,120,20170612,电器),(被罩,70,20170612,床上用品),(音响,420,20170612,电器),(床单,55,20170612,床上用品),(草莓,34,20170612,水果);--注意,按分组条件分组后每一组只会显示第一条记录--group by字句其后可以接多个列名也可以跟having子句,对group by 的结果进行筛选。--按位置字段筛选select * from order_menu group by 5;--练习对购物表按类名分组后显示每一组商品的价格总和select class,SUM(price)from order_menu group byclass;--练习对购物表按类名分组后显示每一组商品价格总和超过150的商品select class,SUM(price)from order_menu group byclassHAVING SUM(price)150;/*having 和 where两者都可以对查询结果进行进一步的过滤差别有1where语句只能用在分组之前的筛选having可以用在分组之后的筛选2使用where语句的地方都可以用having进行替换3having中可以用聚合函数where中就不行。*/--GROUP_CONCAT() 函数SELECT id,GROUP_CONCAT(name),GROUP_CONCAT(JS) from ExamResult GROUP BYid;--(6)聚合函数 先不要管聚合函数要干嘛先把要求的内容查出来再包上聚合函数即可。--(一般和分组查询配合使用)--1 统计表中所有记录--COUNT(列名)统计行的个数--统计一个班级共有多少学生先查出所有的学生再用count包上select count(*) fromExamResult;--统计JS成绩大于70的学生有多少个select count(JS) from ExamResult where JS70;--统计总分大于280的人数有多少select count(name) fromExamResultwhere (ifnull(JS,0)ifnull(Django,0)ifnull(OpenStack,0))280;--注意:count(*)统计所有行; count(字段)不统计null值.--SUM(列名)统计满足条件的行的内容和--统计一个班级JS总成绩先查出所有的JS成绩再用sum包上select JS as JS总成绩 fromExamResult;select sum(JS) as JS总成绩 fromExamResult;--统计一个班级各科分别的总成绩select sum(JS) asJS总成绩,sum(Django) asDjango总成绩,sum(OpenStack) as OpenStack fromExamResult;--统计一个班级各科的成绩总和select sum(ifnull(JS,0)ifnull(Django,0)ifnull(Database,0))as 总成绩 fromExamResult;--统计一个班级JS成绩平均分select sum(JS)/count(*) fromExamResult ;--注意sum仅对数值起作用否则会报错。--AVG(列名)--求一个班级JS平均分先查出所有的JS分然后用avg包上。select avg(ifnull(JS,0)) fromExamResult;--求一个班级总分平均分select avg((ifnull(JS,0)ifnull(Django,0)ifnull(Database,0)))fromExamResult ;--Max、Min--求班级最高分和最低分(数值范围在统计中特别有用)select Max((ifnull(JS,0)ifnull(Django,0)ifnull(OpenStack,0)))最高分fromExamResult;select Min((ifnull(JS,0)ifnull(Django,0)ifnull(OpenStack,0)))最低分fromExamResult;--求购物表中单价最高的商品名称及价格---SELECT id, MAX(price) FROM order_menu;--id和最高价商品是一个商品吗?SELECT MAX(price) FROMorder_menu;--注意null 和所有的数计算都是null所以需要用ifnull将null转换为0-------ifnull(JS,0)--with rollup的使用--2 统计分组后的组记录--(7) 重点Select from where group by having order by--Mysql在执行sql语句时的执行顺序--from where select group by having order by--分析:select JS as JS成绩 from ExamResult where JS成绩 70; ---- 不成功select JS as JS成绩 from ExamResult having JS成绩 90; --- 成功--(8) limitSELECT * from ExamResult limit 1;SELECT * from ExamResult limit 2,5;--跳过前两条显示接下来的五条纪录SELECT * from ExamResult limit 2,2;--- (9) 使用正则表达式查询SELECT * FROM employee WHERE emp_name REGEXP ^yu;SELECT * FROM employee WHERE emp_name REGEXP yun$;SELECT * FROM employee WHERE emp_name REGEXP m{2};外键如果公共关键字在一个关系中是主关键字那么这个公共关键字被称为另一个关系的外键。由此可见外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表具有此外键的表被称为主表的从表。外键又称作外关键字。外键用来链接两张表--- 每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任----主表CREATE TABLEClassCharger(idTINYINT PRIMARY KEYauto_increment,nameVARCHAR (20),ageINT,is_marriged boolean--show create table ClassCharger: tinyint(1));INSERT INTO ClassCharger (name,age,is_marriged) VALUES (冰冰,12,0),(丹丹,14,0),(歪歪,22,0),(姗姗,20,0),(小雨,21,0);----子表CREATE TABLEStudent(idINT PRIMARY KEYauto_increment,nameVARCHAR (20),charger_idTINYINT, --切记:作为外键一定要和关联主键的数据类型保持一致--[ADD CONSTRAINT charger_fk_stu]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)) ENGINEINNODB;INSERT INTO Student(name,charger_id) VALUES (alvin1,2),(alvin2,4),(alvin3,1),(alvin4,3),(alvin5,1),(alvin6,3),(alvin7,2);DELETE FROM ClassCharger WHERE name冰冰;INSERT student (name,charger_id) VALUES (yuan,1);--删除居然成功,可是 alvin3显示还是有班主任id1的冰冰的;-----------增加外键和删除外键---------ALTER TABLE student ADD CONSTRAINTabcFOREIGN KEY(charger_id)REFERENCESclasscharger(id);ALTER TABLE student DROP FOREIGN KEY abc;--外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update--外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对--应匹配行的候选键时,父表的行为取决于在定义子表的外键时指定的--on update/on delete子句-----------------innodb支持的四种方式--------------------------------------------cascade方式 在父表上update/delete记录时同步update/delete掉子表的匹配记录-----外键的级联删除如果父表中的记录被删除则子表中对应的记录自动被删除--------FOREIGN KEY (charger_id) REFERENCESClassCharger(id)ON DELETE CASCADE------set null方式 在父表上update/delete记录时将子表上匹配记录的列设为null--要注意子表的外键列不能为not nullFOREIGN KEY (charger_id) REFERENCESClassCharger(id)ON DELETE SET NULL------Restrict方式 :拒绝对父表进行删除更新操作(了解)------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键--进行update/delete操作(了解)表记录操作(多表查询)--准备两张表--company.employee--company.departmentcreate tableemployee(emp_idint auto_increment primary key not null,emp_namevarchar(50),ageint,dept_idint);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 tabledepartment(dept_idint,dept_namevarchar(100));insert into department values(200,人事部),(201,技术部),(202,销售部),(203,财政部);mysql select * fromemployee;---------------------------------| emp_id | emp_name | age | dept_id |---------------------------------| 1 | A | 19 | 200 || 2 | B | 26 | 201 || 3 | C | 30 | 201 || 4 | D | 24 | 202 || 5 | E | 20 | 200 || 6 | F | 38 | 204 |---------------------------------rows in set (0.00sec)mysql select * fromdepartment;--------------------| dept_id | dept_name |--------------------| 200 | 人事部 || 201 | 技术部 || 202 | 销售部 || 203 | 财政部 |--------------------rows in set (0.01 sec)准备两张表多表查询之连接查询mysql SELECT * FROMemployee,department;--select employee.emp_id,employee.emp_name,employee.age,--department.dept_name from employee,department;-----------------------------------------------------| emp_id | emp_name | age | dept_id | dept_id | dept_name |-----------------------------------------------------| 1 | A | 19 | 200 | 200 | 人事部 || 1 | A | 19 | 200 | 201 | 技术部 || 1 | A | 19 | 200 | 202 | 销售部 || 1 | A | 19 | 200 | 203 | 财政部 || 2 | B | 26 | 201 | 200 | 人事部 || 2 | B | 26 | 201 | 201 | 技术部 || 2 | B | 26 | 201 | 202 | 销售部 || 2 | B | 26 | 201 | 203 | 财政部 || 3 | C | 30 | 201 | 200 | 人事部 || 3 | C | 30 | 201 | 201 | 技术部 || 3 | C | 30 | 201 | 202 | 销售部 || 3 | C | 30 | 201 | 203 | 财政部 || 4 | D | 24 | 202 | 200 | 人事部 || 4 | D | 24 | 202 | 201 | 技术部 || 4 | D | 24 | 202 | 202 | 销售部 || 4 | D | 24 | 202 | 203 | 财政部 || 5 | E | 20 | 200 | 200 | 人事部 || 5 | E | 20 | 200 | 201 | 技术部 || 5 | E | 20 | 200 | 202 | 销售部 || 5 | E | 20 | 200 | 203 | 财政部 || 6 | F | 38 | 204 | 200 | 人事部 || 6 | F | 38 | 204 | 201 | 技术部 || 6 | F | 38 | 204 | 202 | 销售部 || 6 | F | 38 | 204 | 203 | 财政部 |-----------------------------------------------------笛卡尔积查询内连接--查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。select * from employee,department where employee.dept_id department.dept_id;--select * from employee inner join department on employee.dept_id department.dept_id;-----------------------------------------------------| emp_id | emp_name | age | dept_id | dept_id | dept_name |-----------------------------------------------------| 1 | A | 19 | 200 | 200 | 人事部 || 2 | B | 26 | 201 | 201 | 技术部 || 3 | C | 30 | 201 | 201 | 技术部 || 4 | D | 24 | 202 | 202 | 销售部 || 5 | E | 20 | 200 | 200 | 人事部 |-----------------------------------------------------外连接--(1)左外连接在内连接的基础上增加左边有右边没有的结果select * from employee left join department on employee.dept_id department.dept_id;-----------------------------------------------------| emp_id | emp_name | age | dept_id | dept_id | dept_name |-----------------------------------------------------| 1 | A | 19 | 200 | 200 | 人事部 || 5 | E | 20 | 200 | 200 | 人事部 || 2 | B | 26 | 201 | 201 | 技术部 || 3 | C | 30 | 201 | 201 | 技术部 || 4 | D | 24 | 202 | 202 | 销售部 || 6 | F | 38 | 204 | NULL | NULL |-------------------------------------------------------(2)右外连接在内连接的基础上增加右边有左边没有的结果select * from employee RIGHT JOIN department on employee.dept_id department.dept_id;-----------------------------------------------------| emp_id | emp_name | age | dept_id | dept_id | dept_name |-----------------------------------------------------| 1 | A | 19 | 200 | 200 | 人事部 || 2 | B | 26 | 201 | 201 | 技术部 || 3 | C | 30 | 201 | 201 | 技术部 || 4 | D | 24 | 202 | 202 | 销售部 || 5 | E | 20 | 200 | 200 | 人事部 || NULL | NULL | NULL | NULL | 203 | 财政部 |-------------------------------------------------------(3)全外连接在内连接的基础上增加左边有右边没有的和右边有左边没有的结果--mysql不支持全外连接 full JOIN--mysql可以使用此种方式间接实现全外连接select * from employee RIGHT JOIN department on employee.dept_id department.dept_idUNIONselect * from employee LEFT JOIN department on employee.dept_id department.dept_id;-----------------------------------------------------| emp_id | emp_name | age | dept_id | dept_id | dept_name |-----------------------------------------------------| 1 | A | 19 | 200 | 200 | 人事部 || 2 | B | 26 | 201 | 201 | 技术部 || 3 | C | 30 | 201 | 201 | 技术部 || 4 | D | 24 | 202 | 202 | 销售部 || 5 | E | 20 | 200 | 200 | 人事部 || NULL | NULL | NULL | NULL | 203 | 财政部 || 6 | F | 38 | 204 | NULL | NULL |-------------------------------------------------------注意 union与union all的区别union会去掉相同的纪录多表查询之符合条件连接查询--查询员工年龄大于等于25岁的部门SELECT DISTINCTdepartment.dept_nameFROMemployee,departmentWHERE employee.dept_id department.dept_idAND age25;--以内连接的方式查询employee和department表并且以age字段的升序方式显示selectemployee.emp_id,employee.emp_name,employee.age,department.dept_namefromemployee,departmentwhere employee.dept_id department.dept_idorder by age asc;多表查询之子查询--子查询是将一个查询语句嵌套在另一个查询语句中。--内层查询语句的查询结果可以为外层查询语句提供查询条件。--子查询中可以包含IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字--还可以包含比较运算符 、 !、 、--1. 带IN关键字的子查询---查询employee表但dept_id必须在department表中出现过select * fromemployeewhere dept_id IN(select dept_id fromdepartment);---------------------------------| emp_id | emp_name | age | dept_id |---------------------------------| 1 | A | 19 | 200 || 2 | B | 26 | 201 || 3 | C | 30 | 201 || 4 | D | 24 | 202 || 5 | E | 20 | 200 |---------------------------------rows in set (0.01sec)--2. 带比较运算符的子查询--、!、、、--查询员工年龄大于等于25岁的部门select dept_id,dept_name fromdepartmentwhere dept_id IN(select DISTINCT dept_id from employee where age25);--3. 带EXISTS关键字的子查询--EXISTS关字键字表示存在。在使用EXISTS关键字时内层查询语句不返回查询的记录。--而是返回一个真假值。Ture或False--当返回Ture时外层查询语句将进行查询当返回值为False时外层查询语句不进行查询select * fromemployeeWHERE EXISTS(SELECT dept_name from department where dept_id203);--department表中存在dept_id203Tureselect * fromemployeeWHERE EXISTS(SELECT dept_name from department where dept_id205);--Empty set (0.00 sec)ps:create table t1(select * from t2);MySQL之索引索引简介索引在MySQL中也叫做“键”是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键尤其是当表中的数据量越来越大时索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表如果要查某个字如果不使用音序表则需要从几百页中逐页去查。索引特点创建与维护索引会消耗很多时间与磁盘空间但查询速度大大提高索引语法--创建表时--语法CREATE TABLE表名 (字段名1 数据类型[完整性约束条件…],字段名2 数据类型[完整性约束条件…],[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY[索引名] (字段名[(长度)] [ASC |DESC]));----------------------------------创建普通索引示例CREATE TABLEemp1 (idINT,nameVARCHAR(30) ,resumeVARCHAR(50),INDEXindex_emp_name (name)--KEY index_dept_name (dept_name));--创建唯一索引示例CREATE TABLEemp2 (idINT,nameVARCHAR(30) ,bank_numCHAR(18) UNIQUE,resumeVARCHAR(50),UNIQUE INDEXindex_emp_name (name));--创建全文索引示例CREATE TABLEemp3 (idINT,nameVARCHAR(30) ,resumeVARCHAR(50),FULLTEXTINDEXindex_resume (resume));--创建多列索引示例CREATE TABLEemp4 (idINT,nameVARCHAR(30) ,resumeVARCHAR(50),INDEXindex_name_resume (name, resume));------------------------------------添加索引---CREATE在已存在的表上创建索引CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX索引名ON 表名 (字段名[(长度)] [ASC |DESC]) ;---ALTER TABLE在已存在的表上创建索引ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX索引名 (字段名[(长度)] [ASC |DESC]) ;CREATE INDEX index_emp_name onemp1(name);ALTER TABLE emp2 ADD UNIQUE INDEXindex_bank_num(band_num);--删除索引语法DROP INDEX 索引名 on表名DROP INDEX index_emp_name onemp1;DROP INDEX bank_num on emp2;索引测试实验--创建表create table Indexdb.t1(id int,name varchar(20));--存储过程delimiter $$create procedureautoinsert()BEGINdeclare i int default 1;while(i500000)doinsert into Indexdb.t1 values(i,yuan);set ii1;end while;END$$delimiter ;--调用函数call autoinsert();--花费时间比较--创建索引前select * from Indexdb.t1 where id300000;--0.32s--添加索引create index index_id onIndexdb.t1(id);--创建索引后select * from Indexdb.t1 where id300000;--0.00spython和mysqlpython关于mysql的APIpymysql模块pymsql是Python中操作MySQL的模块其使用方法和py2的MySQLdb几乎相同。模块安装pip install pymysql执行sql语句importpymysql#添加数据conn pymysql.connect(host127.0.0.1, port3306, userroot, passwd, dbyyy)cursorconn.cursor()#sql CREATE TABLE EMPLOYEE (#FIRST_NAME CHAR(20) NOT NULL,#LAST_NAME CHAR(20),#AGE INT,#SEX CHAR(1),#INCOME FLOAT )# #cursor.execute(sql)#row_affected cursor.execute(create table t1(id INT ,name VARCHAR(20)))#row_affectedcursor.execute(INSERT INTO t1(id,name) values (1,alvin),(2,xialv))#cursor.execute(update t1 set name silv2 where id2)#查询数据row_affectedcursor.execute(select * from t1)onecursor.fetchone()#manycursor.fetchmany(2)#allcursor.fetchall()#scroll#cursor.scroll(-1,moderelative) # 相对当前位置移动#cursor.scroll(2,modeabsolute) # 相对绝对位置移动#更改获取数据结果的数据类型,默认是元组,可以改为字典等:conn.cursor(cursorpymysql.cursors.DictCursor)conn.commit()cursor.close()conn.close()事务事务命令事务指逻辑上的一组操作组成这组操作的各个单元要不全部成功要不全部不成功。数据库开启事务命令start transaction开启事务Rollback 回滚事务,即撤销指定的sql语句(只能回退insert deleteupdate语句)回滚到上一次commit的位置Commit提交事务提交未存储的事务avepoint 保留点 事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)转账实例UPDATE account set balancebalance-5000 WHERE name”yuan”;UPDATE account set balancebalance5000 WHERE name”xialv”;savepointcreate table test2(id int PRIMARY KEY auto_increment,name VARCHAR(20)) engineinnodb;INSERT INTOtest2(name) VALUE (alvin),(yuan),(xialv);starttransaction;insert into test2 (name)values(silv);select * fromtest2;commit;--保留点starttransaction;insert into test2 (name)values(wu);savepoint insert_wu;select * fromtest2;delete from test2 where id4;savepoint delete1;select * fromtest2;delete from test2 where id1;savepoint delete2;select * fromtest2;rollback todelete1;select * from test2;python中调用数据库启动事务的方式importpymysql#添加数据conn pymysql.connect(host127.0.0.1, port3306, userroot, passwd, dbyyy)cursorconn.cursor()try:insertSQL0INSERT INTO ACCOUNT2 (name,balance) VALUES (oldboy,4000)insertSQL1UPDATE account2 set balancebalance-30 WHERE nameyuaninsertSQL2UPDATE account2 set balancebalance30 WHERE namexialvcursorconn.cursor()cursor.execute(insertSQL0)conn.commit()cursor.execute(insertSQL1)raiseExceptioncursor.execute(insertSQL2)cursor.close()conn.commit()exceptException as e:conn.rollback()conn.commit()cursor.close()conn.close()事务特性1 原子性(Atomicity)原子性是指事务是一个不可分割的工作单位事务中的操作要么都发生要么都不发生。2 一致性(Consistency)事务前后数据的完整性必须保持一致。在事务执行之前数据库是符合数据完整性约束的无论事务是否执行成功事务结束后的数据库中的数据也应该是符合完整性约束的。在某一时间点如果数据库中的所有记录都能保证满足当前数据库中的所有约束则可以说当前的数据库是符合数据完整性约束的。比如删部门表前应该删掉关联员工(已经建立外键)如果数据库服务器发生错误有一个员工没删掉那么此时员工的部门表已经删除那么就不符合完整性约束了所以这样的数据库也就性能太差啦3隔离性(Isolation)事务的隔离性是指多个用户并发访问数据库时一个用户的事务不能被其它用户的事务所干扰多个并发事务之间数据要相互隔离。4持久性(Durability)持久性是指一个事务一旦被提交它对数据库中数据的改变就是永久性的接下来即使数据库发生故障也不应该对其有任何影响。三、隔离性将数据库设计为串行化程的数据库让一张表在同一时间内只能有一个线程来操作。如果将数据库设计为这样那数据库的效率太低了。所以数据库的设计这没有直接将数据库设计为串行化而是为数据库提供多个隔离级别选项使数据库的使用者可以根据使用情况自己定义到底需要什么样的隔离级别。不考虑隔离性可能出现的问题--一个事务读取到了另一个事务未提交的数据,这是特别危险的要尽力防止。a 1000b1000a:starttransactionupdate set moneymoney100 where nameb;b:starttransaction;select * from account where nameb;--1100commit;a:rollback;b: starttransaction;select * from account where nameb;--1000脏读--在一个事务内读取表中的某一行数据多次读取结果不同。(一个事务读取到了另一个事务已经提交--的数据--增加记录、删除记录、修改记录)在某写情况下并不是问题在另一些情况下就是问题。a:starttransaction;select 活期账户 from account where nameb;--1000 活期账户1000select 定期账户 from account where nameb--1000 定期账户:1000select 固定资产 from account where nameb--1000 固定资产1000------------------------------b:starttransaction;update set money0 where nameb;commit;------------------------------select 活期定期固定 from account where nameb; --2000 总资产 2000不可重复读是指在一个事务内读取到了别的事务插入的数据导致前后读取不一致。(一个事务读取到了另一个事务已经提交的数据---增加记录、删除记录)在某写情况下并不是问题在另一些情况下就是问题。b1000c2000d3000a:starttransactionselect sum(money) from account;---3000 3000-------------------d:start transaction;insert into account values(d,3000);commit;-------------------select count(*)from account;---3 33000/3 1000 1000虚读四个隔离级别Serializable可避免脏读、不可重复读、虚读情况的发生。(串行化)Repeatable read可避免脏读、不可重复读情况的发生。(可重复读)不可以避免虚读Read committed可避免脏读情况发生(读已提交)Read uncommitted最低级别以上情况均无法保证。(读未提交)安全性考虑SerializableRepeatable readRead committedRead uncommitted数据库效率Read uncommittedRead committedRepeatable readSerializable一般情况下我们会使用Repeatable read、Read committed mysql数据库默认的数据库隔离级别Repeatable readmysql中设置数据库的隔离级别语句set [global/session] transaction isolation level xxxx;如果使用global则修改的是数据库的默认隔离级别所有新开的窗口的隔离级别继承自这个默认隔离级别如果使用session修改则修改的是当前客户端的隔离级别和数据库默认隔离级别无关。当前的客户端是什么隔离级别就能防止什么隔离级别问题和其他客户端是什么隔离级别无关。mysql中设置数据库的隔离级别语句select tx_isolation;
