数据操作
一、数据类型
1、数字（默认都是有符号,宽度指的是显示宽度，与存储无关）
（1）tinyint [unsigned][zerofill] (1个字节存)
有符号：
-128~~127
无符号：
0~~255
（2） int [unsigned][zerofill] （4个字节存）
有符号：
-2147483648~~2147482647
无符号：
0~~4294967295
（3）bigint[unsigned][zerofill] （8个字节存）
有符号：
-9223372036854775808~~9223372036854775808
无符号：
0~~494967295
2、字符（宽度指的是字符个数 与存储有关）：
char ：定长(简单粗暴，不够则凑够固定长度存放起来，浪费空间，存取速度快)
varchar： 变长（精准，计算除待存放的数据长度，节省空间，存取速度慢）
3、日期
#注册时间
datatime 2017-09-06 10:39:46
#出生年月日 ，开学时间
data:2017-09-06
#聊天记录，上课时间
time:10:39:46
#出生年
year:2017
4、枚举与集合
enum枚举：规定一个范围，可有多个值，但是为该字段船只是，只能去规定范围中的一个
set集合：规定一个范围，可有多个值，但是为该字段船只是，只能去规定范围中的一个或多个
1：
整型测试
create table t1(id tinyint);
create table t2(id int);
create table t3(id bigint) ;
#测试
create table t4(salary float(5,2)); insert into t4 values(3.735); insert into t4 values(3.735684);
2、char 与 varcahr测试
create table t6(name char(4)); insert into t6 values('alex') insert into t6 values('欧德博爱')； insert into t6 values('艾利克斯')； create table t7(x char(5),y varchar(5)); insert into t7 values('addd','dsds') #char_length：查看字符长度 insert into t7 values('你好啊'，'好你妹')#char_length：查看字符长度
了解
insert into t7 values('你好啊'，'好你妹')#length:查看字节长度
select char_length(x),char_length(y) from t7;
注意两点：
insert into t7 values('abc','abc');#length:查看字节长度
select * from t7 where y='abc '; #去掉末尾的空格然后去比较
3、日期
create table student( id int , name char(5), born_date date, born_year year, reg_time datetime, class_time time ); insert into student values(1,'alex',now(),now(),now(),now()); insert into student values(1,'alex','2017-09-06','2017','2017-09-06 10:09:36','09:06:36');
4、枚举与集合
create table student1( id int primary key auto_increment, name char(5), sex enum('male','female'), hobbies set('music','read','coding') ); insert into student1(name,sex,hobbies) values('egon','male','music,read,coding');
1 简单查询 select * from employee; select name,salary from employee; 2 where条件 select name,salary from employee where salary > 10000; select name,salary from employee where salary > 10000 and salary < 20000; select name,salary from employee where salary between 10000 and 20000; select name,salary from employee where salary not between 10000 and 20000; select name,salary from employee where salary = 10000 or salary = 20000 or salary = 30000; select name,salary from employee where salary in (10000,20000,30000); select * from employee where salary = 10000 or age = 18 or sex='male'; select * from employee where post_comment is Null; select * from employee where post_comment = Null; select * from employee where post_comment is not Null; select * from employee where name like '%n%'; select * from employee where name like 'e__n'; 3 group by分组 mysql> select depart_id,group_concat(name) from employee group by depart_id; +-----------+--------------------------------------------------------------+ | depart_id | group_concat(name) | +-----------+--------------------------------------------------------------+ | 1 | egon,alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | | 2 | 歪歪,丫丫,丁丁,星星,格格 | | 3 | 张野,程咬金,程咬银,程咬铜,程咬铁 | +-----------+--------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select depart_id,count(id) from employee group by depart_id; +-----------+-----------+ | depart_id | count(id) | +-----------+-----------+ | 1 | 8 | | 2 | 5 | | 3 | 5 | +-----------+-----------+ 3 rows in set (0.01 sec) mysql> select depart_id,group_concat(id) from employee group by depart_id; +-----------+------------------+ | depart_id | group_concat(id) | +-----------+------------------+ | 1 | 1,2,3,4,5,6,7,8 | | 2 | 9,10,11,12,13 | | 3 | 14,15,16,17,18 | +-----------+------------------+ 3 rows in set (0.00 sec) mysql> select depart_id,count(id) from employee group by depart_id; +-----------+-----------+ | depart_id | count(id) | +-----------+-----------+ | 1 | 8 | | 2 | 5 | | 3 | 5 | +-----------+-----------+ 3 rows in set (0.00 sec) mysql> select depart_id,max(salary) from employee group by depart_id; +-----------+-------------+ | depart_id | max(salary) | +-----------+-------------+ | 1 | 1000000.31 | | 2 | 4000.33 | | 3 | 20000.00 | +-----------+-------------+ 3 rows in set (0.00 sec) mysql> select depart_id,min(salary) from employee group by depart_id; +-----------+-------------+ | depart_id | min(salary) | +-----------+-------------+ | 1 | 2100.00 | | 2 | 1000.37 | | 3 | 10000.13 | +-----------+-------------+ 3 rows in set (0.00 sec) mysql> select depart_id,sum(salary) from employee group by depart_id; +-----------+-------------+ | depart_id | sum(salary) | +-----------+-------------+ | 1 | 1070200.64 | | 2 | 13001.47 | | 3 | 84000.13 | +-----------+-------------+ 3 rows in set (0.00 sec) mysql> select depart_id,avg(salary) from employee group by depart_id; +-----------+---------------+ | depart_id | avg(salary) | +-----------+---------------+ | 1 | 133775.080000 | | 2 | 2600.294000 | | 3 | 16800.026000 | +-----------+---------------+ 3 rows in set (0.00 sec)
