一、创建表
需要信息：表名，表字段名，表字段的定义
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;
MySQL学习笔记六：表的创建及管理易佰、 赛兔、 tradeindia、 继意大利，德法疫情濒临失控！欧洲陷入新一轮恐慌…、 eBay张元：出口商最终目的是打造品牌、 Yandex快速链接：让你的广告快人一步！、 （精品分析）亚马逊英国站垃圾袋类目市场调查数据报告、 Wish将开启线上培训 对疫情期间物流渠道把控进行解读、
No comments:
Post a Comment