mysql创建表，修改表操作_mysql创建表的长度括号怎么打-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_40087726/article/details/114020845

1.建表

# 建表 
# 建表使用的小括号，不是大括号，所有的列都是声明在小括号中的
# 每个声明的列字段，都是逗号结尾，如果最后一列后面没有内容，不能写逗号（写了后面就必须加内容）
# 列字段的分词，都是统一用下划线（包括表名）
# 同一个数据库中，表名不能重复（不同数据库可以出现同名表）
# 列定义的顺序：不是固定，一般推荐就是：列名 数据类型 [属性，可以多个属性] [索引] [注释]等，都是使用空格分隔
# int(5)，整数类型后面的小括号中指定的是显示的长度（不是占用字节数），一般都是跟自动补0属性使用，范围是固定的，就是4个字节数值范围
# varchar(20)，字符串类型，后面的小括号中指定的是字符（以前代表字节），可变字符串（不是自动扩充意思），列中的值为可变长字符串。长度可以指定为0到65,535之间的值。不会自动补空格，但是不能超出指定字符范围
# double(10,2)，浮点类型，后面小括号指定两个参数，第一个是总长度（包含小数），第二个是小数位，取值范围是8字节（16位）
# decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。适用于对精度要求较高的字段，区别：浮点型在数据库中存放的是近似值，会四舍五入，而定点类型在数据库中存放的是精确值。 
# char(3):定长字符串，3个字节，列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度.
# auto_increment 自增属性，一般都是主键列使用，默认初始值是1，每次增加步长也是1
# unsigned 无符号，值不能为负数，最小值是0（如果当前列是外键，必须对应表也要是无符号）
# zerofill 和 not null 顺序不能颠倒，zerofill一般跟现实固定长度的整数配合使用，不足显示长度，左侧补0
# comment '注释'，MySQL中，单引号代表字符串，可以加在列后，也可以加在表名后
# primary key 主键，约束：非空，且唯一
# 表名和字段名，在mysql中，不区分大小写
create table if not exists `bill`(
	`bill_id` int(5) auto_increment primary key comment '订单号',
	`prod_name` varchar(20) comment '商品名称',
	`bill_count` tinyint(1) unsigned not null comment '购买数量',
	`bill_price` double(10,2) comment '订单价格',
	`bill_time` datetime not null comment '订单时间',
	`bill_email` varchar(50) not null default '123@123.com' comment '订单邮箱',
	`bill_state` int(2) zerofill not null comment '订单状态'
)engine=innodb default charset=utf8 auto_increment=100 comment '订单表';

2.修改表

# 修改表名：alter table 表名 rename as 新的表名
alter table `bill` rename as `bill_info`;

# 修改表，增加字段 alter table add 新列名 类型 属性 注释等
alter table `bill_info` add `bill_tel` varchar(11) not null comment '订单手机';

# 修改表，修改字段 alter table modify 列名 新的类型 新的属性
# 注意：原始数据的类型要跟新的类型要兼容（一般都是扩容），最好不加默认值
alter table `bill_info` modify `bill_state` char(3);

# 更改列名，必须要指定新列名
alter table `bill_info` change `prod_name` `bill_name` varchar(10) comment '订单名称';

# 修改表，删除列字段 alter table 表名 drop 列名
alter table `bill_info` drop `bill_email`;

# 更新数据：update
# 语法：update 表名 set 列名=值[, 列名1=值1, .....] [where 条件]
# 每次只能进行单表更新，不能多表操作，如果没有找到更新的数据，不会报错，只是返回的影响行数为0
# 修改学生手机号

# 删除数据：delete 
# 语法：delete from 表名 [where 条件];
# 全表删除，不带条件（一般不用这么用，必须带条件，养成习惯（除非是自己的环境））
delete from grade_bak;
# delete删除数据，自增序号是不会重置的，而是从之前最大值开始继续自增，逐条删除
insert into grade_bak(grade_name) values('预科班');

# 删除数据前，要备份数据
# 年级数据迁移或者备份
drop table if exists grade_bak;
create table if not exists grade_bak(
	grade_id int(4) auto_increment primary key comment '年级编号',
	grade_name varchar(10) not null comment '年级名称'
)engine=innodb auto_increment=1 default charset=utf8 comment '年级表';
# 数据迁移或者数据备份：方式1：使用Navicat工具，导出表结构和数据（sql脚本）（导出必须会），单独执行，方式2：借助insert+select批量插入
insert into grade_bak(grade_id, grade_name) select grade_id, grade_name from grade;
select * from grade_bak;

# 清空表数据：truncate
# 语法：truncate [table] 表名;
# 直接将全表数据清空，执行效率高于delete（不带条件），对事务无影响，会自动重建自增列计数器（auto_increment），表结构，索引等不影响
# delete区别：delete可以删除部分数据，但是truncate删除所有数据，delete不会重建自增计数器，二者对表结构，索引，约束不影响
truncate table grade_bak;
# truncate grade_bak;

3.查询

# 查询工资大于12000的员工信息（使用concat连接两个字段）
select employee_id, concat(first_name, '.', last_name) 'employee_name', salary, department_id 
from employees where salary >= 12000;

# 是否为空: 不是用=，而是 is null
# 查询员工表中，没有绩效的员工信息
select * from employees where commission_pct is null;

# 判断不为空：is not null 不是not is null
# 查询员工表中，有绩效的员工信息
select * from employees where commission_pct is not null;

# in : select .. from table where 列 in (值1, 值2, ...)
# 凡是在小括号内出现的值，在数据过滤时，会返回true
# 查询部门20, 30, 40, 50, 80的部门员工信息
select * from employees where department_id = 20 or department_id = 30 or department_id = 40 or department_id = 50 or department_id = 80;
select * from employees where department_id in (20, 30, 40, 50, 80);
# 查询不在部门20, 30, 40, 50, 80的部门员工信息
select * from employees where department_id not in (20, 30, 40, 50, 80);

4.连接

# 连接查询：table_1 [INNER] JOIN table_2 ON 连接条件;（前提：关联表之间必须有相同含义的关联字段）
# 等值连接查询：只匹配多表中都存在的记录(等值匹配)，一般都会给表起别名（区分重复列，如果查询列都是唯一，可以不加别名，建议加），join关键字可以写多个（join后只能跟一个表）
# 查询所有员工的编号，姓名，工资，部门编号，部门名称
# 1 使用join
select e.employee_id, e.last_name, e.salary, e.department_id, d.department_name
from employees e join departments d on e.department_id = d.department_id;

# 从subjects和grades数据表查询课程名称和所属年级名称
select s.subj_no, s.subj_name, s.class_hour, g.grade_name
from subjects s join grade g on s.grade_id = g.grade_id;

# 2 from 多表，使用逗号分隔，where中增加等值连接条件
select e.employee_id, e.last_name, e.salary, e.department_id, d.department_name
from employees e, departments d where e.department_id = d.department_id;

# 外连接 : 如果使用等值连接查询，会导致不等值的某些数据丢失，查询结果有误
#update subjects set grade_id = null where subj_name = 'C#基础';
# 左【右】外连接查询：如果是左外，左表示主表，所有数据必须是显示完整的，即便在右表中没有匹配值，右外就是右表是主表，必须显示完整
select s.subj_no, s.subj_name, s.class_hour, g.grade_name
from subjects s left join grade g on s.grade_id = g.grade_id;

select s.subj_no, s.subj_name, s.class_hour, g.grade_name
from subjects s right join grade g on s.grade_id = g.grade_id;

# 自连接（表本身跟自己连接,表本身要有关联字段，常用语权限表）
# 查询Austin人的上级的信息（编号，姓名，工资，部门）
select e1.manager_id, e2.last_name, e2.salary, e2.department_id from employees e1, employees e2
where e1.manager_id = e2.employee_id and e1.last_name = 'Austin';

5.order by使用

# 查询4-6名的学生信息
select st.stu_no, st.stu_name, su.subj_name, re.stu_result from subjects su, result re, students st where su.subj_no = re.subj_no and re.stu_no = st.stu_no
and su.subj_name = '数据库结构-2' order by re.stu_result desc
# pageNo=2, pageSize=3
# limit (2-1)*3, 3;
limit 3, 3;

6.系统函数

# mysql的系统函数
# 统计函数：count(..)，计数器，有一条记录就加1，跟给什么值没有关系
# 建议：不要使用count(*)，尤其是大表-数据量比较大，推荐使用count(1)，count(主键列)
# 如果是count(某列)，该列中的null值是不会计数

# 单行函数
# 1）数值处理-一般处理都是数值列
# round-四舍五入， mod-求余，ceil-向上取整，floor-向下取整, rand-随机数，truncate-截取
select round(123.55, 1), round(123.66), mod(11,4), ceil(234.2), floor(234.2), rand(), truncate(123.55, 1) from dual;


# 2) 字符串处理
# concat-拼接, substring,substr-字符串截取(不是下标0开始), length-字符串长度，lower-小写，upper-大写，replace-字符替换
select concat('hello', '-', 'mysql') from dual;
select substring('hellomysql', 2) from dual;
select substring('hellomysql', 2, 4) from dual;
select substr('hellomysql', 2) from dual;
select substr('hellomysql', 2, 4) from dual;
select length('hellomysql') from dual;
select lower(first_name), upper(last_name) from employees;
select replace('hellomysql','l','L') from dual;

# 3）日期处理函数
select now(), sysdate(), year(now());
select date_format(now(), '%Y年%m月%d日 %H:%i:%s');
select version(), current_user(), current_date(), current_time() from dual;
select datediff(now(), hire_date), now(), hire_date from employees;

# 4）null值处理函数，null值跟任何数进行运算，结果都是null
# 计算公司所有人的年薪
select employee_id, last_name, salary, (salary + salary * ifnull(commission_pct, 0)) * 12 from employees;

7.索引

# 删除唯一约束（索引）
show index from emp3;
# 方式1
drop index emp3_name_email_uk on emp3;
# 方式2
alter table emp3 drop index emp3_name_email_uk;

# MySQL的索引 - 索引的执行人为是干预不了的，只能尽可能的让数据库使用索引，至于用没用，是数据库自己确定的
# 分类：普通索引 index, 唯一索引 unique, 主键索引 primary key, 全文索引 fulltext

# 员工表
create table t_user(
	u_id int(11) primary key auto_increment,
	u_name varchar(32) not null,
	index t_user_name_index (u_name)
)engine=innodb default charset=utf8;

# 添加索引的方式2 
alter table t_user add index t_user_name_index (u_name);

8.外键

# 外键约束 foreign key 
create table dept1(
	dept_id int(6) primary key auto_increment,
	dept_name varchar(32) not null
)engine=innodb default charset=utf8;

create table emp5(
	emp_id int(6),
	emp_name varchar(32) not null,
	emp_email varchar(100),
	dept_id int(6),
	constraint emp5_name_email_uk unique(emp_email),
	constraint emp5_id_pk primary key(emp_id),
	constraint emp5_dept_id_fk foreign key(dept_id) references dept1(dept_id)
)engine=innodb default charset=utf8;

	constraint emp6_name_email_uk unique(emp_email),
	constraint emp6_id_pk primary key(emp_id),
	constraint emp6_dept_id_fk foreign key(dept_id) references dept1(dept_id) on delete cascade