MySQL学习笔记六：表的创建及管理
一、创建表
需要信息：表名，表字段名，表字段的定义
create table table_name 列定义 选项;
create table table_name like old_table_name; --like:包括旧表的结构+信息+索引
create table table_name select * from old_table_name; --包括旧表的结构+信息
create table studyt1 ( id int(20) unsigned auto_increment not null, name varchar(20) not null, jobdate date, primary key (id) ) engine=innodb default charset=utf8; show create table studyt1;
二、查询表
show tables;
show tables from db_name;
show tables like '关键字';
show talbes form db_name like '关键字';
show tables; show tables from test; show tables like 'bl%'; show tables from test like 'bl%';
三、查看表内容
select * from study01; select * from test.study01; select id01,id02 from test.study01 limit 2;
四、表的增删改查
查数据库是否是自动commit
show variables like '%autocommit%';
测试表和数据
create table study11 (id int(3),name varchar(12),sex varchar(6)); create table study12 (id int(3),name varchar(12),age int(5)); insert into study11 (id,name,sex) values (1,'study01','男'), (2,'study02','男'), (3,'study03','女'), (4,'study04','女'), (5,'study05','女'); insert into study12 (id,name,age) values (1,'study01',20), (2,'study02',21), (3,'study03',18), (4,'study04',19), (5,'study05',28);
4.1、insert
语法：insert into table_name (表字段) values (值列表);
/*方法1*/ insert into study11 (id,name,sex) values (6,'study06','男');/*方法2*/ insert into study11 values (7,'study07','男'); /*方法3*/ insert into study11 set id=8,name='study06',sex='男'; /*方法4*/ insert into study1 values (1,'study01',now(),20);/*方法5*/ insert into study1 values (2,'study02',default,20); /*方法6*/ create table study13 select * from study11;truncate table study13; insert into study13 select * from study11;
4.2、delete
语法：delete from 表名 [where 条件] [order by] [limit row_count];
delete from study13 where id=1; delete from study13 limit 2;
4.3、update
语法：update 表名 set 列名=值 where 条件;
update study13 set name='study11',sex='女' where id=1;
4.4、select
语法：select 字段/表名 from 表名/视图名 where 查询条件;
查询条件：
1)where 条件
2)group by 分组
3)having 分组后再聚合
4)limit 限制多少行显示
5)order by [asc|desc] 排序，升|降
4.4.1、列连接
select name,concat(name,'-',sex) as '姓名+性别' from study11;
4.4.2、别名 as/也可以省略
select a.* from study11 a,study12 b where a.name=b.name;
4.4.3、虚拟表dual
select now() from dual;
4.4.4、SQL语句注释方式
1）语句前注释：#
#select now() from dual;
2）语句后注释：--
select now() from dual; --查当前系统时间
3）多行注释：/**/
/*select now() from dual;*/
4.4.5、常用的运算符
=：等于
>：大于
<：小于
>=：大于等于
<=：小于等于
<>：不等于
!=：不等于
is null：为null
is not null：不为null
[not]like：模糊查询
[not]between and：在什么范围内
[not]in：在什么范围值内
select * from study12 where age=20; select * from study12 where age<>20; select * from study12 where age>20; select * from study12 where age>=20; select * from study12 where age<20; select * from study12 where age<=20; select * from study12 where age between 18 and 20; select * from study12 where age not between 18 and 20; select * from study12 where age>=18 and age<=20; select * from study12 where age>=18 && age<=20; select * from study12 where age in (18,19,28); select * from study12 where age not in (18,19,28); select * from study12 where name like 'study%'; select * from study12 where name not like '%005%';
4.4.6、逻辑运算
非：not
与：and &&
或：or
异或：xor
select null is not not null,null is null; select null<=>null,10<=>null;
4.4.7、组合
select * from study12 where name='study01' and age=20; select * from study12 where name='study01' or age=21;
4.4.8、like
select * from study12 where name like 'study%';
4.4.9、查询分组与排序
group by 分组
group by 列 {asc升序|desc降序},{with rollup} 组内聚合计算
select left(name,4),group_concat(name) name from study12 group by left(name,4);
4.4.10、limit
限制返回的行数
select * from study12 order by age desc limit 1;
4.4.11、distinct
去除重复记录
select distinct left(name,4) name from study12;
4.4.12、union
无重并集：把多个结果组合并去重，再以第1列的结果进行升序排序。
select name from study11 union select name from study12;
4.4.13、union all
有重并集：把多个结果组合不去重
select name from study11 union all select name from study12;
4.4.14、for update
会锁表(生产环境不要轻易用)
select * from study11 for update;
