MySQL数据库基本操作

数据库常用操作

功能	sql
查看所有数据库	show databases;
创建数据库	create? database [if not exists] mydb1 [charset=utf8]
切换数据库	use mydb1;
删除数据库	drop database?[if exists]?mydb1；
修改数据库编码	alter database mydb1 character set utf8;

创建表格式

create table [if not exists]表名(
  字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
  字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
  字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];

use mydb1;
create  table if not exists student(
    sid int,
    name varchar(20),
    gender varchar(20),
    age int,
    birth date, 
    address varchar(20),
    score double
);

数值类型

字符串类型

日期类型

对表结构其他操作

功能	sql
查看当前数据库所有名称	show tables;
查看指定某个表创建的语句	show create table 表名;
查看表结构	desc 表名;
删除表	drop table 表名;

修改表结构

alter table 表名 add 列名 类型(长度) [约束];

alter table 表名 change 旧列名 新列名 类型(长度) 约束; 

eg：

#为student表的dept字段更换为department varchar(30)

ALTER TABLE student change `dept` department VARCHAR(30); 

修改表删除列

alter table 表名 drop 列名;

修改表名

rename table 表名 to 新表名;

数据插入

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些
insert into 表 values (值1,值2,值3...);     //向表中插入所有列

数据修改

update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

eg:

-- 将所有学生的地址修改为重庆 
update student set address = '重庆’; 

-- 讲id为1004的学生的地址修改为北京 
update student set address = '北京' where id = 1004 

-- 讲id为1005的学生的地址修改为北京，成绩修成绩修改为100 
update student set address = '广州',score=100 where id = 1005

数据删除

delete from 表名 [where 条件];
truncate table  表名 或者 truncate 表名

注意：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table ，可以理解为是将整个表删除，然后再创建该表；

MySQL约束

分类

• 主键约束
• 自增长约束
• 非空约束
• 唯一性约束
• 默认约束
• 零填充约束
• 外键约束

MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。

主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。

每个表最多只允许一个主键

主键约束的关键字是：primary key

当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

单列主键

创建单列主键有两种方式：一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键。

-- 在 create table 语句中，通过 PRIMARY KEY 关键字来指定主键。
--在定义字段的同时指定主键，语法格式如下：
create table 表名(
   ...
   <字段名> <数据类型> primary key 
   ...
)

eg:

create table emp1(
    eid int primay key,
    name VARCHAR(20),
    deptId int,
    salary double
);

多列主键（联合主键）

所谓联合主键，就是这个主键由一张表中多个字段组成。

create table 表名(
   ...
   primary key （字段1，字段2，…,字段n)
);

create table emp3( 
  name varchar(20), 
  deptId int, 
  salary double, 
  primary key(name,deptId) 
);

删除主键约束

格式：

alter table <数据表名> drop primary key;

自增长约束

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。通过给字段添加auto_increment属性来实现主键自增长。

字段名 数据类型?auto_increment

create table t_user1( 
  id int primary key auto_increment, 
  name varchar(20) 
);

默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。
一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
auto_increment约束的字段必须具备 NOT NULL 属性。
auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等。
auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

非空约束

MySQL 非空约束（not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

方式1：<字段名><数据类型> not null;
方式2：alter table 表名 modify 字段 类型 not null;

-- 方式1，创建表时指定
create table t_user6 ( 
  id int , 
  name varchar(20) not null, 
  address varchar(20) not null 
);

create table t_user7 ( 
  id int , 
  name varchar(20) , -- 指定非空约束 
  address varchar(20) -- 指定非空约束 
); 
alter table t_user7 modify name varchar(20) not null; 
alter table t_user7 modify address varchar(20) not null;

删除非空约束

-- alter table 表名 modify 字段 类型 
alter table t_user7 modify name varchar(20) ; 
alter table t_user7 modify address varchar(20) ;

唯一约束

创建唯一约束

方式1：<字段名> <数据类型> unique
方式2： alter table 表名 add constraint 约束名 unique(列);

-- 创建表时指定
create table t_user8 ( 
 id int , 
 name varchar(20) , 
 phone_number varchar(20) unique -- 指定唯一约束 
);

删除唯一约束

-- alter table <表名> drop index <唯一约束名>;
alter table t_user9 drop index unique_ph;

默认约束

MySQL 默认值约束用来指定某列的默认值。

方式1： <字段名> <数据类型> default <默认值>;
方式2: alter table 表名 modify 列名 类型 default 默认值; 

添加默认约束

create table t_user10 ( 
  id int , 
  name varchar(20) , 
  address varchar(20) default ‘北京’ -- 指定默认约束 
);

删除默认约束

-- alter table <表名> modify column <字段名> <类型> default null; 

alter table t_user11 modify column address varchar(20) default null;

基本查询

语法

select 
  [all|distinct]
  <目标列的表达式1> [别名],
  <目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名> 
[having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

select *| 列名 from 表 where 条件

比较运算符

逻辑运算符

排序查询

select 
 字段名1，字段名2，……
from 表名
order by 字段名1 [asc|desc]，字段名2[asc|desc]……

特点

1.asc代表升序，desc代表降序，如果不写默认升序

2.order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名

3.order by子句，放在查询语句的最后面。LIMIT子句除外

-- 1.使用价格排序(降序)
select * from product order by price desc;
-- 2.在价格排序(降序)的基础上，以分类排序(降序)
select * from product order by price desc,category_id asc;
-- 3.显示商品的价格(去重复)，并排序(降序)
select distinct price from product order by price desc;

聚合函数

-- 1 查询商品的总条数
select count(*) from product;
-- 2 查询价格大于200商品的总条数
select count(*) from product where price > 200;
-- 3 查询分类为'c001'的所有商品的总和
select sum(price) from product where category_id = 'c001';
-- 4 查询商品的最大价格
select max(price) from product;
-- 5 查询商品的最小价格
select min(price) from product;
-- 6 查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';

1、count函数对null值的处理

如果count函数的参数为星号（*），则统计所有记录的个数。而如果参数为某字段，不统计含null值的记录个数。

2、sum和avg函数对null值的处理

这两个函数忽略null值的存在，就好象该条记录不存在一样。

3、max和min函数对null值的处理

?max和min两个函数同样忽略null值的存在。

分组查询

分组查询是指使用group by字句对查询信息进行分组

select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

-- 1 统计各个分类商品的个数
select category_id ,count(*) from product group by category_id ;

分页查询

-- 方式1-显示前n条
select 字段1，字段2... from 表明 limit n
-- 方式2-分页显示
select 字段1，字段2... from 表明 limit m,n
m: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
n: 整数，表示查询多少条数据

-- 查询product表的前5条记录 
select * from product limit 5 

-- 从第4条开始显示，显示5条 
select * from product limit 3,5

Insert into select语句

将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句 。

insert into Table2(field1,field2,…) select value1,value2,… from Table1 或者：
insert into Table2 select * from Table1

要求目标表Table2必须存在

将一张表的数据导入到另一张表中，有两种选择 SELECT INTO 和 INSERT INTO SELECT 。

SELECT vale1, value2 into Table2 from Table1

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。

正则表达式