mysql increament_MySQL基本操作

MySQL之基本操作

数据库操作

创建数据库

create database db1;

create database db1 default charset gbk;

create databaseif not exists db1 default character set utf8;

显示创建信息

show create database db1;

修改数据库默认的字符格式

alter database db1 default character set gbk;

删除数据库

drop database db1;

区别：delete、truncate、drop

delete：删除数据

1、数据操作语言(DML)在事务控制里，DML语句是需要commit，不提交的话可以rollback；删除大量记录速度慢，只删除数据不回收高水位线

2、可以带条件删除

truncate：删除数据

1、数据定义语言(DDL)清大量数据速度快，高水位线(high water mark)下降

2、不能带条件truncate

drop：对象表、库、用户等；数据定义语言

数据查询语句：select

通配符：

%匹配0个或任意多个字符

_ 匹配一个字符

＝ 精确匹配

like 模糊匹配

regexp (. 任意单个字符*前导字符出现0次或连续多次 .* 任意长度字符.....) 使用正则表达式来匹配

排序：

order by 排序，默认升序

asc 升序排列结果

desc 降序排列结果

group by 聚合

distinct 去除重复的行

常用函数

查看mysql支持字符加密函数：

select password('123');

select md5('123');

select sha1('123');

select encrypt('123'); 基本上不用了

使用select来调度mysql中的常见函数：

select version(); 　　 当前数据库版本

select current_user(); 当前用户

select current_time(); 当前时间

select current_date(); 当前日期

select now(); 　　　　　 当前日期时间

MySQL表操作

表是数据库存储数据的基本单位，由若干个字段组成，主要用来存储数据记录。表的操作

包括创建表、查看表、修改表和删除表。

语法：

create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

)[存储引擎 字符集];

说明：

auto_increment表示自增长

primary key表示主键

not null表示不为空

==在同一张表中，字段名是不能相同 ==宽度和约束条件可选 ==字段名和类型是必须的

创建表

表school.student1

表school.student1

字段 字段 字段

id name sex age1 tom male 23记录2 jack male 21记录3 alice female 19记录

mysql> CREATE DATABASE school; //创建数据库school

mysql>use school;

mysql>create table student1(->id int,-> name varchar(50),-> sex enum('m','f'),->age int->);

Query OK, 0 rows affected (0.03 sec)

mysql> show tables; //查看表(当前所在库)+------------------+

| Tables_in_school |

+------------------+

| student1 |

+------------------+

1 row in set (0.00 sec)

查看表

mysql> select * from student1; //查询表中所有字段的值

Empty set (0.00sec)

mysql> select name,age from student1; //查询表中指定字段的值

Empty set (0.00 sec)

查询表中所有字段的值

向表中插入内容

语法：

insert into 表名(字段1,字段2...) values(字段值列表...);

表school.student2

字段名 　　数据类型

编号 　　id int

姓名 name varchar(50)

出生年份 　　 born_year 　　 year

生日 birthday date

上课时间 class_time time

注册时间 reg_time datetime

mysql>create table student2(->id int,-> name varchar(50),->born_year year,->birthday date,->class_time time,->reg_time datetime->);

mysql>desc student2;

mysql> insert into student2 values(1,'tom',now(),now(),now(),now());

mysql> insert into student2 values(2,'jack',1982,19821120,123000,20140415162545);

表school.student3

id id int

姓名 name varchar(50)

性别 sex enum('male','female')

爱好 hobby set('music','book','game','disc')

mysql>create table student3(->id int,-> name varchar(50),-> sex enum('male','female'),-> hobby set('music','book','game','disc')->);

mysql>desc student3;

mysql>show create table student3\G

mysql> insert into student3 values (1,'tom','male','book,game');

mysql> insert into student3 values (2,'jack','male','film');

mysql> select * from student3;

查看表结构

DESCRIBE查看表结构

DESCRIBE 表名;

DESC 表名;

SHOW CREATE TABLE查看表详细结构

SHOW CREATE TABLE 表名;

表完整性约束

作用：用于保证数据的完整性和一致性

约束条件 说明

PRIMARY KEY (PK) 　　标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE+NOT NULL

FOREIGN KEY (FK) 　　标识该字段为该表的外键，实现表与表(父表主键/子表1外键/子表2外键)之间的关联

NOT NULL 标识该字段不能为空

UNIQUE KEY (UK) 　　标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY

AUTO_INCREMENT 　　　标识该字段的值自动增长(整数类型，而且为主键)

DEFAULT 为该字段设置默认值

UNSIGNED 　 无符号，正数

ZEROFILL 使用0填充，例如0000001

说明：1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

sex enum('male','female') not null default 'male'age int unsigned NOT NULL default20必须为正值(无符号) 不允许为空 默认是203. 是否是key

主键 primary key

外键 forengn key

索引 (index,unique...)

DEFAULT、NOT NULL

表school.student4

mysql>create table school.student4(-> id int notnull,-> name varchar(50) notnull,-> sex enum('m','f') default 'm' notnull,-> age int unsigned default 18 not null, hobby set('music','disc','dance','book') default 'book,dance');

mysql> insert into student4 values(1,'jack','m',20,'book');

Query OK,1 row affected (0.00sec)

mysql> insert into student4(id,name) values(2,'robin');

Query OK,1 row affected (0.00 sec)

设置唯一约束 UNIQUE

表company.department

mysql>create table company.department(->dept_id int,-> dept_name varchar(30) unique,-> comment varchar(50));

设置主键约束 PRIMARY KEY

primary key 字段的值是不允许重复，且不允许不NULL(UNIQUE + NOT NULL)

单列做主键

多列做主键(复合主键)

单列做主键

表school.student6 方法一

mysql>create table student6(-> id int primary key notnull auto_increment,-> name varchar(50) notnull,-> sex enum('male','female') not null default 'male',-> age int not null default 18);

Query OK, 0 rows affected (0.20sec)

mysql> insert into student6 values (1,'alice','female',22);

mysql>insert into student6(name,sex,age) values-> ('jack','male',19),-> ('tom','male',23);

mysql> select * fromstudent6;+----+-------+--------+-----+

| id | name | sex | age |

+----+-------+--------+-----+

| 1 | alice | female | 22 |

| 2 | jack | male | 19 |

| 3 | tom | male | 23 |

+----+-------+--------+-----+

3 rows in set (0.00 sec)

表school.student7 方法二

mysql>create table student7(-> id int auto_increment notnull,-> name varchar(50) notnull,-> sex enum('male','female') not null default 'male',-> age int not null default 18,->primary key(id));

Query OK, 0 rows affected (0.21 sec)

复合主键

表school.service

host_ip 存储主机IP

service_name 服务名

port 服务对应的端口

allow(Y,N) 服务是否允许访问

主键： host_ip+ port =primary key

mysql>create table service(-> host_ip varchar(15) notnull,-> service_name varchar(10) notnull,-> port varchar(5) notnull,-> allow enum('Y','N') default 'N',->primary key(host_ip,port)->);

Query OK, 0 rows affected (0.00 sec)

mysql> insert into service values ('192.168.2.168','ftp','21','Y');

Query OK,1 row affected (0.09sec)

mysql> insert into service values ('192.168.2.168','httpd','80','Y');

Query OK,1 row affected (0.03 sec)

设置字段值增 AUTO_INCREMENT

表company.department3

CREATE TABLE department3 (

dept_id INT PRIMARY KEY AUTO_INCREMENT,

dept_name VARCHAR(30),

comment VARCHAR(50)

);

设置外键约束 FOREIGN KEY

父表company.employees

mysql>create table employees(-> name varchar(50) notnull,-> mail varchar(20),-> primary key(name)) engine=innodb;

子表company.payroll

mysql>create table payroll(-> id int notnull auto_increment,-> name varchar(50) notnull,-> payroll float(10,2) notnull,->primary key(id),->foreign key(name)->references employees(name)->on update cascade->on delete cascade-> )engine=innodb;

mysql> select * fromemployees;+------+--------------+

| name | mail |

+------+--------------+

| tom | 11111@qq.com |

+------+--------------+

1 row in set (0.00sec)

mysql> select * frompayroll;+----+------+---------+

| id | name | payroll |

+----+------+---------+

| 1 | tom | 11.00 |

+----+------+---------+

1 row in set (0.00sec)

mysql> update employees set name='tomaaa' where name='tom';

Query OK,1 row affected (0.04sec)

Rows matched:1 Changed: 1Warnings: 0

mysql> select * frompayroll;+----+--------+---------+

| id | name | payroll |

+----+--------+---------+

| 1 | tomaaa | 11.00 |

+----+--------+---------+

1 row in set (0.00sec)

mysql> select * fromemployees;+--------+--------------+

| name | mail |

+--------+--------------+

| tomaaa | 11111@qq.com |

+--------+--------------+

1 row in set (0.00sec)

mysql> delete from employees where name='tomaaa';

Query OK,1 row affected (0.04sec)

mysql> select * fromemployees;

Empty set (0.00sec)

mysql> select * frompayroll;

Empty set (0.00 sec)

View Code

mysql> update employees set name='tomaaa' where name='tom';

mysql> delete from employees where name='alice';

结论：

当父表中某个员工的记录修改时，子表也会同步修改

当父表中删除某个员工的记录，子表也会同步删除

修改表ALTER TABLE

语法：

修改表名

ALTER TABLE 表名 RENAME 新表名;

增加字段

ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST;

ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名

删除字段

ALTER TABLE 表名 DROP 字段名;

修改字段

ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

实例：

1. 修改存储引擎

mysql>alter table service-> engine=innodb; //engine=myisam|memore|....2. 添加字段

mysql>create table student10 (id int);

mysql>alter table student10-> add name varchar(20) notnull,-> add age int not null default 22;

mysql>alter table student10-> add stu_num int not null after name; //添加name字段之后

mysql>alter table student10-> add sex enum('male','female') default 'male' first; //添加到最前面3. 删除字段

mysql>alter table student10->drop sex;

mysql>alter table service->drop mac;4. 修改字段类型modify

mysql>alter table student10->modify age tinyint;

mysql>alter table student10-> modify id int not null primary key ; //修改字段类型、约束、主键5. 增加约束(针对已有的主键增加auto_increment)

mysql> alter table student10 modify id int not null primary key auto_increment; //错误，该字段已经是primary key

ERROR1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int notnull auto_increment;

Query OK, 0 rows affected (0.01sec)

Records: 0 Duplicates: 0 Warnings: 06. 增加复合主键

mysql>alter table service2->add primary key(host_ip,port);7. 增加主键

mysql>alter table student1->add primary key(id);8. 增加主键和自动增长

mysql>alter table student1-> modify id int notnull primary key auto_increment;9. 删除主键[primary key auto_increment]

a. 删除自增约束

mysql> alter table student10 modify id int notnull;

b. 删除主键

mysql>alter table student10-> drop primary key;

修改表示例

复制表

复制表结构＋记录 (key不会复制: 主键、外键和索引)

mysql> create table new_service select * from service;

只复制表结构

mysql> create table new1_service select * from service where 1=2; //条件为假，查不到任何记录

复制表结构，包括Key

mysql> create table t4 like employees;