2ndDay+SQL语句简述+MYSQL实践操作

 
 

  
  sql语句
 
 

相关知识了解
代码讲解站点

开源中国
博客园
CSDN
传智播客
51CTO

关系型数据库
Datebasee
MySql
Oracle
db2
sqlserver

nosql（非关系型数据库）
oceanBase(蚂蚁金融研发,分布式关系型数据库)
mongodb（非关系型数据库）


MYSQL数据类型讲解

MySQL数据类型	含义（有符号）

date	3字节，日期，格式：2014-09-18
time	3字节，时间，格式：08:42:30
datetime	8字节，日期时间，格式：2014-09-18 08:42:30
timestamp	4字节，自动存储记录修改的时间
year	1字节，年份

整型
tinyint	1字节，范围（-128~127）
smallint	2字节，范围（-32768~32767）
mediumint	3字节，范围（-8388608~8388607）
int	4字节，范围（-2147483648~2147483647）
bigint	8字节，范围（+-9.22*10的18次方）
上面定义的都是有符号的，也可以加上unsigned关键字，定义成无符号的类型，
比如：
tinyint unsigned的取值范围为0~255

浮点型
MySQL数据类型	含义
float(m, d)	4字节，单精度浮点型，m总个数，d小数位
double(m, d)	8字节，双精度浮点型，m总个数，d小数位
decimal(m, d)	decimal是存储为字符串的浮点数


字符串数据类型
MySQL数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	可变长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

1.char（n）和varchar（n）中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入m个中文，但是实际会占用m*3个字节。
2.同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n。
3.超过char和varchar的n设置后，字符串会被截断。
4.char的上限为255字节，varchar的上限65535字节，text的上限为65535。
5.char在存储的时候会截断尾部的空格，varchar和text不会。
6.varchar会使用1-3个字节来存储长度，text不会。



创建表
CREATE TABLE table1(colm1 datatype,colm2 datatype ,......)

修改表
rename table1to table2
删除表
DROP TABLE table1
添加新列
ALTER TABLE table1 add(colum1 datatype)
修改表中列的名字
alter table 表名称 change 字段名称 新字段名称 字段类型 [是否允许非空];
删除表中某一列
ALTER TABLE table1 DROP colum
（alter table 表名 drop column 列名;）
添加主键： 
Alter table tabname add primary key(col)
删除主键： 
Alter table tabname drop primary key(col)
创建索引：
create [unique] index idxname on tabname(col….)

删除索引：
drop index idxname
注：索引是不可更改的，想更改必须删除重新建。

创建视图：
create view viewname as select statement

几个简单的基本的sql语句
选择：select * from table1 where 范围

插入：insert into table1(field1,field2) values(value1,value2)
   
删除记录：delete from table1 where 范围

修改，更新：update table1 set field1=value1 where 范围
（update users set password="123456"；
 update users set password="123456qq",flag=1 where sex=1；
update users set flag=3,password="we123456" where sex=1 and flag=1
）
查询，查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!

消除重复：
select DISTINCT username from users；
select DISTINCT username,password from users

排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

使用外连接
A、left （outer） join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right （outer） join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full/cross （outer） join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

【常用SQL语句】具体用法

举例所用表
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '部门编号',
  `dname` varchar(15) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(50) DEFAULT NULL COMMENT '部门所在位置',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COMMENT='部门表';



DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` double(10,0) unsigned NOT NULL COMMENT '雇员编号',
  `ename` varchar(15) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(10) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(10) unsigned DEFAULT NULL COMMENT '雇员对应的领导的编号',
  `hiredate` date DEFAULT NULL COMMENT '雇员的雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '雇员的基本工资',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(10) unsigned DEFAULT NULL COMMENT '所在部门',
  PRIMARY KEY (`empno`),
  KEY `deptno` (`deptno`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='雇员表';



DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(10) unsigned DEFAULT NULL COMMENT '工资等级',
  `losal` int(10) unsigned DEFAULT NULL COMMENT '此等级的最低工资',
  `hisal` int(10) unsigned DEFAULT NULL COMMENT '此等级的最高工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级表';



//一、增加

1、创建表
create table odk(
odkId int,
odkname varchar(255),
odkpwd varchar(255),
odksex int,
intr int
)
2、插入记录
INSERT INTO odk(odkname,odkpwd,odksex,intr) VALUES
("zhang","123456",1,4)插入单行记录
或者
INSERT INTO odk(odkname,odkpwd,odksex,intr) VALUES
("zhang","123456",1,4),
 ("hxua","123456",1,4),
("huxan","123456",1,4),
("hnxg","123456",1,4),
("anxg","123456",1,4),
("hxg","123456",1,4)插入多行记录

3、增加新的字段
alter table odk add(inot int)

二、//删除

4、删除表
DROP table softwear
5、删除字段
alter table odk DROP inot
6、删除记录
delete from odk where odkId=5 

三、//修改

7、修改表名
rename table odk to okm
rename table okm to odk

8、修改列名
alter table odk change inot infor varchar(255)null

9修改记录
update odk set odkname="lisi",odksex=0 where odksex=1 and intr=4

四、//查询
10、like查询（模糊查询）
select * from table1 where field1 like ’%value1%’
（1select*from users where sex like '0'；
2select password,username from users where flag=0；
3select password 用户密码,username 用户名 from users where flag=0//为字段取别名

select * from emp where ename like '%S';  “%”百分号表示多个字符
select * from emp where ename like '%S%';查询ename包含S的所有列
select * from emp where ename like '_M%'，“_”下划线表示一个字符

）
11、
消除重复：
select DISTINCT username from users；
select DISTINCT username,password from users

12、范围查询
select * from salgrade where losal<>1201/不等于，<>与!=在mysql里都表示不等于
select * from salgrade where losal!=1201;//不等于
select * from salgrade where losal<>1201;
select * from salgrade where losal<=1201;
select * from salgrade where losal>1201;
select * from salgrade where losal>=1201;
select * from emp where empno >=7600 and empno <=7900//查询多个范围值
select * from emp where empno BETWEEN 7600 AND 7800//查询多个范围值
select * from emp where empno in(7654,7788)//当empno为字符串时要加""引号，指定范围
select * from emp where empno not in(7654,7788)
select * from emp where empno=7654 or empno=7788 or empno=7782//指定行记录

13、查询记录为空值，不为空值和记录为Null
select * from emp where comm is null//为null时
select * from emp where comm=" "//为空时
select * from emp where comm is not null;//不为空

14、升序和降序
select * from users  ORDER BY flag ASC;//升序为ASC,默认升序，可以不写ASC：select * from users  ORDER BY flag
select * from users  ORDER BY flag DESC;

15、查询显示为大写或者小写
select LOWER(ename) from emp;//小写LOWER
select UPPER(LOWER(ename)) from emp//大写UPPER

16、函数查询
select count(*) 总员工,avg(sal)平均工资,MAX(sal)最高工资,min(sal)最低工资,sum(sal)总工资 from emp//五个主函数

17、分组查询
select deptno,count(*) from emp GROUP BY deptno//注意搭配主函数的字段是否合适，例如，我的学校叫张三！
select deptno,count(*) from emp GROUP BY deptno HAVING deptno>=20//用HAVING不用where

18、子查询
select * from emp where sal > (select AVG( sal) from emp );
select * from users where userId in (select userId from users where userId >=5)

19、length查询
select LENGTH(ename),ename from emp;查询出指定字段ename所有记录的长度

【20、***连表查询***】important
20.1、连表查询1
select e.ename,d.deptno from emp e,dept d where e.deptno=d.deptno
//emp和dept为不同的两张表，连表查询emp的ename和dept的deptno，e，d是给emp,和dept取得别名，
e.deptno=d.deptno是连表的关系
也可以写为：
select e.*,d.deptno from emp e,dept d where e.deptno=d.deptno//e.*表示查询emp所有列
20.2、连表查询2
select e.*,d.dname from emp e INNER JOIN dept d on e.deptno=d.deptno

21、分页查询
select* from table limit A，B，A表示从第A+1行开始，显示B行记录
select * from emp LIMIT 10,6 

未完待续，看下一天