mysql基础教程(独家整理).doc

mysql数据库(第1版) 小编：郑保珍 为什么要使用数据库：海量存储高速查询1.登录与服务：登录： mysql -uroot -proot (登录本机mysql服务器) mysql -uroot -proot -h 192.168.10.1 (登录ip为192.168.10.1的mysql服务器) 退出： exit q quit Ctrl+c服务： net stop mysql 停止mysql服务 net start mysql 启动mysql服务 net restart mysql 重启mysql服务C： 注意最后没有封号。2:mysql编码： set names gbk|utf8 ：临时编码设置，当再次进入mysql中的时候将再次转变为默认编码：status; s ：查看编码show variables like charac% ：查看编码Server characterset: utf8 服务器编码Db characterset: utf8 当前数据库编码Client characterset: utf8 客户编码Conn. characterset: utf8 客户连接编码character_set_client | utf8 客户编码character_set_connection | utf8 连接编码character_set_database | utf8 当前数据库编码character_set_results | utf8 结果集character_set_server | utf8 服务器character_set_system | utf8 系统3.database操作：1 查看和切换：显示当前所有数据为名称：show databases; show schemas();查看当前使用的数据库：select database();切换或进入当前数据库：use db_name; 2 创建：create database|schema if not exists db_name default character set = 字符集; 查看：show create database db_name; 删除：drop database db_name4.data类型数值 tinyint 1个字节 有符号：-128 到127 ，无符号：0-255smallint 2个字节 3.27w正负mediumint 3个字节 3000w int 4个字节 21亿正负 int 和 int(5) 都是4个字节 21亿正负，基本无区别。 bigint 8个字节 总结，int(M) zerofill，加上zerofill后M才表现出有点点效果，比如 int(3) zerofill,你插入到数据库里的是10,则实际插入为010,也就是在前面补充加了一个0.如果int(3)和int(10)不加 zerofill,则它们没有什么区别不是用来限制int个数的int(M)的最大值和最小值与undesigned有关，如下图：mysql create table t (t int(3) zerofill);mysql insert into t set t = 10;mysql select * from t;+| t |+| 010 |+mysql create table t (t int zerofill);mysql insert into t set t = 10;mysql select * from t;+| t |+| 0000000010 |+mysql create table t (t int);mysql insert into t set t = 10;mysql select * from t;+| t |+| 10 |+ 其实当我们在选择使用int的类型的时候，不论是int(3)还是int(11)，它在数据库里面存储的都是4个字节的长度，在使用int(3)的时候如果你输入的是10，会默认给你存储位010,也就是说这个3代表的是默认的一个长度，当你不足3位时，会帮你补全，当你超过3位时，就没有任何的影响。他们之间除了在存储的时候稍微有点区别外，在我们使用的时候是没有任何区别的。int(10)也可以代表2147483647这个值int(11)也可以代表。要查看出不同效果记得在创建类型的时候加 zerofill这个值，表示用0填充，否则看不出效果的。我们通常在创建数据库的时候都不会加入这个选项，所以可以说他们之间是没有区别的。float(M,D) 4或8个字节 M:代表小数的总位数，D代表小数点后的位数Float(6,2) -9999.99到+9999.99M最大位数10的38次方，D的位数也是10的38次方。如果M创建表时创建：create table table_name(属性名 数据类型 unique，deptno int unique,constraint uk_name unique(字段名)unique index|key 索引名 (字段名 长度 asc|desc)unique index index_deptno(deptno);2创建索引：create unique index 索引名 on table_name (属性名 长度 asc|desc)3修改索引：alter table table_name add unique index|key 索引名称(字段);3.普通索引：所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一，非空等限制），该类型的索引可以添加创建在任何数据类型的字段上：启用索引：alter table table_name enable(启用) keys;禁用索引：alter table table_name disable(禁用) keys;查看索引：show indexes from table_name;删除索引：drop index 索引名称 on table_name; alter table table_name drop index 索引名称;单列索引：1创建表时：create table table_name(属性名 数据类型，属性名 数据类型，index|key 索引名 (属性名 长度 asc|desc)index index_deptno(deptno);2创建索引：create index 索引名 on table_name (属性名 长度 asc|desc)3修改索引：alter table table_name add index|key 索引名称(字段);多列索引：1创建表时：create table table_name(属性名 数据类型，deptno int,dname varchar(20),index|key 索引名 (属性名 长度 asc|desc， 属性名 长度 asc|desc)index|key index_deptno_dname(deptno,dname);2创建索引：create index 索引名 on table_name (属性名,属性名,属性名)eg: create index index_deptno_dname on t_dept (deptno,dname)3修改索引：alter table table_name add index|key 索引名称(字段1,字段2,字段3.);eg: alter table test3 add index|key index_deptno_dname(deptno,dname);4.全文索引：全文索引主要关联在数据类型为CHAR、VARCHAR和Text的字段上，以遍能够更加快速的查询数据量较大的字符串类型的字段。Mysql3.23版本开始支持全文索引，只能在存储引起为MyISAM的数据库表上创建全文索引。在默认情况下，全文索引的索引方式不区分大小写。如果全文索引所关联的字段为二进制数据类型的话，则区分大小写。1创建表时：create table table_name(属性名 数据类型，属性名 数据类型，FULLTEXT index|key 索引名 (属性名 长度 asc|desc)FULLTEXT index_deptno(deptno);2创建索引：create FULLTEXT INDEX 索引名 on table_name (属性名 长度 asc|desc)3修改索引：alter table table_name add FULLTEXT INDEX|KEY 索引名称(字段);5.自增长(约束)：auto_increment当数据库表中插入新纪录时，字段上的值会自动生成唯一的ID。在具体设置auto_increment约束的时候，一个数据库表中只能有一个字段使用该约束，该数据类型是整数类型。由于设置auto_increment约束后字段会生成唯一ID，所以该字段也经常设置成PK主键。create table test7(id int unsigned not null auto_increment primary key )auto_increment=100;修改auto_increment的值: alter table table_name auto_increment=值;5.外键(约束)：foreign前面的约束都是针对单个数据表的，外键约束是保证多个表(常为两个表)之间的参照的。比如：有两个表，部门表和雇员表，然后雇员表中有一个表示部门编号的字段deptno，其依赖于部门表的主键，这样字段deptno就是雇员表的外键，通过该字段部门表和雇员表建立了关系。注意：在具体设置FK约束时，设置FK约束的字段必须依赖于数据库中已经存在的父表的主键。同时外键可以为NULL。create table table_name(属性名 数据类型，constraint FK_name foreign key (子表字段) references father_table_name (父表字段)；);alter table table_name add constraint FK_name foreign key (字字段) references father_table_name (父字段)；6.外键(约束)：NULL问题：在见表的时候，列后面not null default , default 0 这里的not null 是什么意思呢？答案：就是让这个列的值不为null。 如果某个列确实没有填值，也有默认值，也不为null。问题：为什么不希望这里的值为null呢？答案：不好比较，null本身是一种类型，比较式只能用专门的is null 和is nut null 来比较，遇到运算符一 律返回null。假设记录有null， 比如select * from books where name=null ，这个时候也无法取到任 何记录。而且null影响索引的效率，也就影响查询的效率。 select * from books where name is not null ：这个时候会把所有name不是null的记录取出来 select * from books where name is null ：这个时候会把所有name是null的记录取出来问题：null和空字符 ,” 有什么去别呢？答案：null就是null，判断是不是null有专门的 is not null 和not null。就像去银行的话，连卡都没有。 “ ” 空字符 ，没有内容，就像去银行，有卡但是没有钱。7.DML:添加记录：insert into table_name(字段名称.) values(字段值.);insert into table_name(字段名称.) values(字段值.),(字段值.);insert into table_name select 字段名称. from table_name1;insert table_name(字段名称) select *|字段名称 from table_nameinsert into database_name1.table_name1 SELECT 字段1，字段二，from database_name2.table_name2;修改记录：update table_name set 字段名称=值. where order by limit;删除记录：delete from table_name where order by limit 慢: 删记录空间保留truncate table table_name; 稍快:删除记录空间回收;alter table table_name auto_increment =1;8.DQL：单表查询操作：简单数据查询：所有字段: select * from table_name; 查询特定字段和其他剩余字段：select other,字段 from table_name;指定字段: select 字段1,字段2 from table_name; 关键字distinct去除重复记录 : select DISTINCT 字段 ,字段 from table_name; 四则运算(+ - * / %)查询: select name ,sal*12 from t_employee; 设置显示格式数据查询，关键字CONCAT链接字符串：select CONCAT(ename,雇员的年薪为：,sal * 12) as yearsalary from tabale_name;select * from emp where hiredate 1998-01-01;select * from emp where year(hiredate)1998;select year(2000-01-11);条件数据查询：between .and：select * from test where age=50 and|&|or| | age200;2. 查询部门人数，最高工资，最低工资，平均工资，工资总和：select count(*) as 部门人数 ,max(salary) as 最高工资 ,min(salary) as 最低工资 ,avg(salary) 平均工资 ,sum(salary) 工资总和 from empG;3. 以dept_Id分组查询：select * from emp group by dept_id;4.查询以dept_id分组后，每组的记录数，和dept_id:select dept_id,count(*) from emp group by dept_id;5.使用with rollup后，把每组的统计数再次统计总和：select dept_id,count(*) as total from emp group by dept_id with rollup;+-+-+| dept_id | total |+-+-+| 1 | 4 | 2 | 3 | 3 | 2 | 4 | 2 | NULL | 11 |+-+-+select * from emp group by dept_id having count(*)3;select dept_id,count(*) as total from emp group by dept_id having count(*)3;select *,count(*) as total from emp group by dept_id having count(*)3;select dept_id,count(*) as 部门人数,min(salary) 最低工资,max(salary) 最高工资,avg(salary) 平均工资from emp group by dept_id having avg(salary)20000;+-+-+-+-+-+| dept_id | 部门人数 | 最低工资 | 最高工资 | 平均工资 |+-+-+-+-+-+| 1 | 4 | 30000.00 | 900000.00 | 280000.000000 | 2 | 3 | 10000.00 | 300000.00 | 170000.000000 | 3 | 2 | 20000.00 | 900000.00 | 460000.000000 | 4 | 2 | 9000.00 | 600000.00 | 304500.000000 |+-+-+-+-+-+mysql select dept_id,count(*) as 部门人数,min(salary) 最低工资,max(salary) 最高工资,avg(salary) 平均工资 from emp group by dept_id having avg(salary)300000 order by dept_id desc;+-+-+-+-+-+| dept_id | 部门人数 | 最低工资 | 最高工资 | 平均工资 |+-+-+-+-+-+| 4 | 2 | 9000.00 | 600000.00 | 304500.000000 | 3 | 2 | 20000.00 | 900000.00 | 460000.000000 |+-+-+-+-+-+select dept_id,count(*) as 部门人数,min(salary) 最低工资,max(salary) 最高工资,avg(salary) 平均工资 from emp group by dept_id having avg(salary)300000 order by dept_id desc limit 1,1;+-+-+-+-+-+| dept_id | 部门人数 | 最低工资 | 最高工资 | 平均工资 |+-+-+-+-+-+| 3 | 2 | 20000.00 | 900000.00 | 460000.000000 |+-+-+-+-+-+inset into result(name,subject,score) values (张三 , 数学 ,90),(张三 , 语文 ,50),(张三 , 地理 ,40),(李四 , 语文 ,55),(李四 , 政治 ,45),(王五 , 政治 ,30),(赵六 , 语文 ,100),(赵六 , 数学 ,99),(赵六 , 品德 ,98),题目：查询出挂科2门以及2门以上的学员的平均分：方案一：逆向思维，先查出所有人的平均分，然后再筛选第一步：查询所有人的平均分。 第二部： 计算出每个人的挂科情况,通过g标志处挂科结果第三部： 挂科数目就是g的sum结果第四部：每个人的平均分以及挂科数已经算出，再having筛选一下即可得出最终结果：方案二：先找出谁的挂科数=2，找到这些人再求这些人的平均分：第一步：找出谁挂科了：查出来的结果可以当成表来看：my