create table t1(i1 tinyint, i2 int);
# 默认创建的所有数据都是有符号的
create table t1(i1 tinyint unsigned, i2 int unsigned);
# unsigned 表示无符号
?
create table t3(f1 float, d1 double);
create table t3(f1 float(7,2)); # 7表示整数和小数部分加起来的长度,2表示小数部分的长度
insert into t3 values(1.6249846464484846)
?
char(25)
# ‘abc ‘ 浪费空间、节省时间
varchar(25)
# ‘3abc‘ 节省空间、浪费时间
create table t5(c1 char(5), c2 varchar(5));
?
时间
create table t6(dt datetime, d date, t time, y year, ts timestamp);
?
约束
create table t8(username char(12), sex enum(‘male‘, ‘famale‘));
create table t9(username char(12), hobby set(‘唱‘,‘跳‘, ‘rab‘, ‘篮球‘));
insert into t9 values(‘caijie‘, ‘唱,跳‘);
# set类型 写入的时候内容要全放在引号里面,当写入内容有重复时会自动去重
数据操作
create table emp2(
id int,
name char(12),
age tinyint unsigned,
gender enum(‘male‘, ‘female‘),
salary float(7,2),
hobby set(‘linux‘, ‘pathon‘, ‘go‘)
);
?
对数据添加
insert into emp values(3, ‘caijie‘, 20, ‘female‘, 50000.00, ‘linux, pathon‘),(4, ‘zengshushu‘, 20, ‘male‘, 50000.00, ‘linux, pathon‘);
?
insert into emp(id, name) values(5, ‘beauty‘),(6, ‘handsome‘); # 只给id name 添加数据
?
insert into eemp2 select * from emp; # 将从emp中拿到的数据添加到emp2中
?
insert into emp2(id, name) select id,name from emp; # 将从emp中拿到的id name 添加到emp2中的id name
补充编码问题:
-
1.临时解决问题 在客户端执行
-
set xxxx = utf8;
-
永久解决问题 在my.ini添加
-
set xxxx = utf8;
-
实时解决问题
-
create table 表名() charset = utf8;
取消当前操作 \c或者‘>‘
单表查询
select
查全部
# select * from 表;
?
指定列查询
# select emp_name,salary from employee;
?
在列中使用四则运算
# select emp_name,salary*12 from employee;
?
重命名 as
# select emp_name,salary*12 as annul_salary from employee;
# select emp_name,salary*12 annul_salary from employee;
?
去重 distinct
# select distinct post from employee;
# select distinct sex,post from employee;
?
函数 concat()拼接
# select concat(‘姓名:‘, emp_name),concat(‘年薪:‘, salary*12) from employee;
?
where 语句 根据条件筛选
比较运算 = > < >= <= !=
# select * from employee where age > 18;
# select * from employee where salary < 10000;
# select * from employee where salary = 20000;
?
between a and b # [a,cb]
# select * from employee where salary between 10000 and 20000;
?
in 一般在某个列表或元组里面
# select * from employee where salary in (17000,20000);
?
like 模糊查询
# _ 通配符 表示一个字符长度的任意内容
# select * from employee where emp_name like ‘cai___‘;
# % 通配符 表示任意字符长度的任意内容
# select * from employee where emp_name like ‘cai%‘;
# select * from empoyee where emp_name like ‘%e‘;
# select * from employee where emp_name like ‘%i%‘;
?
regexp 正则匹配
# select * from employee where emp_name regexp ‘^cai‘;
?
逻辑运算
and 与
# select * from employee where age > 18 and post = ‘teacher‘;
?
or 或
# select * from employee where salary < 10000 or salary > 30000;
?
not 非
# select * from employee where salary nor in (10000,20000,30000);
?
关于null 查询时用is
查看岗位描述为null的员工信息
# select * from employee where post is null;
?
查看岗位描述不为null的员工信息
# select * from employee where post not is null;
?
5个聚合函数(一般和分组聚合连用)
-
sum 求和
-
count 计数
-
min 最小值
-
max 最大值
-
avg 平均数
分组聚合 group by
查询岗位名以及岗位包含的所有的员工名字
# select post,group_concat(emp_name) from employee group by post;
?
查询各个部门年龄在20岁以上的人的平均薪资
# select post,avg(salary) from employee where age > 20 group by post;
?
# select * from 表 where 条件 group by 分组;
?
过滤 having(group by + 聚合函数)
查询平均薪资大于1万的部门
# select post,avg(salary) from employee group by post having avg(salary) > 10000;
?
排序order by
升序(默认为升序)
# select * from employee order by salary;
# select * from employee order by salary asc;
?
降序
# select * from employee order by salary desc;
?
当年纪一样时 再通过薪资排序
# select * from employee order by age,salary;
# select * from employee order by age,salary desc;
# select * from employee order by age desc,salary;
?
limit 取数据
取前n条
# select * from 表 order by 列 limit n;
?
从m+1开始,取n条
# select * from 表 order by 列 limit m,n;
?
从m+1开始,取n条
# select * from 表 order by 列 limit n offset m;
?
整个顺序
# select * from 表 where 条件 group by 分组 having 过滤 order by 排序 limit n;
?
?
原文:https://www.cnblogs.com/womenzt/p/12435960.html
如果您也喜欢它,动动您的小指点个赞吧