mysql数据库增删改查关键字_Mysql数据库的增删改查

数据库：存储数据的仓库

姓名 年龄 性别 生日 入职时间

A 12 男 1999-01-3 2017-05-22

B 34 男

关系型数据库存在磁盘

非关系型数据库存在内存

sqL:

create database xx; 创建数据库

show databases;查看数据库

show create database s3;查看某个数据库

drop database #if exists xx ；修改数据库

alter database xx #character set gbk#;删除数据库

use xx;使用数据库/切换数据库

select database();查询当前使用的数据库

主键，非空且唯一 not null unique

float(4,2) 999,，99

char(3)定长字符

varchar(20)变长字符

TINYTEXT 短文本字符串

BLOB 二进制形式的长文本数据

TEXT 长文本数据

DATE 日期

TIME 时间

auto_increment 主键自动增长

PRIMARY KEY 主键必须包含唯一的值，不能含NULL的值，每个表都应该有一个主键，并且每个表只能有一个主键。

CREATE TABLE emloyee(

id TINYINT PRIMARY KEY auto_increment,

name VARCHAR(25),

gender boolean,

age INT DEFAULT 19,

depattemnt VARCHAR(20),

salary DOUBLE(7,2)

);

表结构：

show tables; 查询表

desc emloyee;展示表的内容

show create table emloyee；展示表的结构

alter table emloyee add is_married tinyint(1); 增加一行

alter table emloyee add entry_date date not null;增加一行

alter table emloyee add A INT,增加两行

add B VARCHAR(20);

alter table emloyee drop B;删除表

alter table emloyee modify age smallint not null default 18 after id;修改某列

alter table emloyee change depattemnt depart varchar(20) after salaru;修改某列的名字

rename table emloyee to employee;修改表名

drop table X;删除表

create table employee(

id TINYINT PRIMARY KEY auto_increment,

name VARCHAR(25),

gender boolean,

age INT DEFAULT 19,

department VARCHAR(20),

salary DOUBLE(7,2)

)

create table ExamReasult(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

JS DOUBLE,

Django DOUBLE,

PS DOUBLE

);

表数据的操作：

insert into (table) (id,name,age) values(x,y,z);指定添加

insert into (table) values(x,y,z);不指定添加

insert into (table) values(x,y,z),添加多行数据

(m,n,b)

(j,k,l)

delete from (table);删除所有数据

delete from (table) where name="tom";删除张三数据

truncate table (table name);删除全部数据

update (table) set age=age+10 ;修改数据

重点：mysql在执行sql语句时执行顺序:from where select group by having order by

select * from (table);查询表数据

select name from (table);查询名字数据;

select distinct name from (table);查询去掉重复的名字

select name as 名字，js+10 as js成绩 , ps+10 as ps成绩；查询表数据命名别名，js,ps数据+10但不更改数据库

select name ,js from examreasult where js!=88;

select name ,js from examreasult where js>99;

select name ,js from examreasult where js between 80 and 90;

select name ,js from examreasult where js(75,98,91);

select name ,js from examreasult where name like "F%" 名字后加%

select name ,js from examreasult where name="ZZX" and js=75;

select name ,js from examreasult order by js; (排序默认asc/desc asc升序desc 降序)

select name ,js from examreasult where js>70 order by js;

select name,JS+Django+PS as 总成绩 from examreasult order by 总成绩 desc;

错误示范：select JS as JS成绩 from examreasult where JS成绩>70 ; ( 看重点,mysql执行语句时顺序)

group by 分组查询:

注意，按分组条件分组后每一组只会显示第一条记录

group by字句，其后可以接多个列名，也可以跟having子句，对group by的结果进行筛选.

按位置筛选

select * from ExamReasult group by 2;

练习：对成绩表按名字分组后，显示每一类名字的JS的分数总和

select NAME,SUM(JS) from ExamResult group by name;;

练习：对成绩表按名字分组后，显示每一类名字的django的分数总和>150的

select name,sum(Django) from examreasult group by name having sum(Django)>150;

注意：having 和 where 两者都可以对查询结果进行进一步的过滤,差别有：

<1>where语句只能用在分组之前的筛选,having可以用在分组之后的筛选；

<2>使用where语句的地方可以用having进行替换

<3>having中用聚合函数，where中就不行

聚合函数:

count(列名):统计行的个数

select count(name) from examreasult where js>70;统计JS成绩大于70的个数

select count(name) from examreasult where(ifnull(Js,0)+ifnull(Django,0)+ifnull(PS,0)) >180;统计总分大于180的人数有多少？

注意：count(*)统计所有行; count(字段)不统计null值

null 和所有的数计算都是null,所以需要用ifnull将null转换为0！ isfull(js,0)

select AVG/(Js) from examreasult;求JS成绩的平均值

select sum(js)/count(name) from examreasult;求JS成绩的平均值

select min(js) from examreasult;最小值

select max(ifnull(js,0)) from examreasult;最大值

select * from examreasult limit 4;显示4条数据

select * from examreasult limit 2,2；跳过两条显示两条

使用正则表达式查询

select * from employee where emp_name REGEXP '^yu';yu开头

select * from employee where emp_name REGEXP 'yun$';yun结尾

select * from employee where emp_name REGEXP 'm{2}';

多表查询之链接查询

create table classcharger(

id TINYINT PRIMARY KEY auto_increment,

name VARCHAR(20),

age INT,

is_married boolean);

create table student(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

charger_id TINYINT,

FOREIGN KEY (charger_id) REFERENCES classcharger(id)

) ENGINE = INNODB; 切记：作为外键一定要和关联主键的数据类型保持一致

classcharger(父表) 与 student(子表) 关联

create table student2(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

charger_id TINYINT

) ENGINE = INNODB;

alter table student2 ADD CONSTRAINT abc 增加外键

FOREIGN KET(charger_id)

REFERENCES classcharger(id);

alter table student2 drop FOREIGN key abc(外键名字);

外键约束对子表的含义：如果在父表中找不到候选键，则不允许在子表中进行insert/update

外键约束对父表的含义：在父表上进行update/delete以更新或删除在子表中有一条或多条对应

匹配行的候选键时，父表的行为取决于；在定义子表的外键时指定的

on update/on delete子句

cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录

外键的级联删除,如果父表中的记录被删除，则子表中对应的记录自动删除-----------

create table C(

id TINYINT PRIMARY KEY auto_increment,

name VARCHAR(20),

age INT,

is_married boolean);

create table student5(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

charger_id TINYINT,

FOREIGN KEY (charger_id) REFERENCES C(id) on DELETE CASCADE

) ENGINE = INNODB;

set null方式 在父表上update/delete记录时，将子表的匹配记录的列设为null

--要注意子表的外键不能为not null

create table student5(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

charger_id TINYINT,

FOREIGN KEY (charger_id) REFERENCES C(id) on DELETE SET NULL

) ENGINE = INNODB;

多表查询之链接查询

链接查询:

内连接： inner join

外连接: left join right join

全连接:full join

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

create table tableB (

id int primary key,

name varchar(20),

tableA_id int

);

select * from tableA,tableB;

select * from tableA,tableB where tableA.id=tableB.tableA_id;内连接查询

select * from tableB inner join tableA on tableB.tableA_id = tableA.id;内连接查询

员工表

create table employee(

emp_id int auto_increment primary key not null,

emp_name varchar(50),

age int,

dept_id int

);

insert into employee(emp_name,age,dept_id) values("A",19,200),

("A",19,200),

("B",26,201),

("C",30,201),

("D",24,202),

("E",20,200),

("F",38,204);

部门表

create table department(

dept_id int ,

dept_name varchar(100)

);

insert into department values(200,"人事部"),

(201,"技术部"),

(202,"销售部"),

(203,"财政部");

select employee.emp_name,department.dept_name from employee,department where employee.dept_id=department.dept_id AND employee.emp_name="A";

select employee.emp_name,department.dept_name from department inner join employee on employee.dept_id=department.dept_id AND employee.emp_name="A";

select employee.emp_name,department.dept_name from employee left join department on employee.dept_id=department.dept_id ;外连接，显示空值 ，左表为主对应

select employee.emp_name,department.dept_name from employee right join department on employee.dept_id=department.dept_id ;外连接，右表为主

select * from employee where dept_id in (select dept_id from department);查employee在部门的ID

select distinct department.dept_name from employee,department where employee.dept_id = department.dept_id AND employee.age>25;查那个部门员工大于25岁

create table AA (select * from employee);复制一张表

带exists关键字的子查询

exists关键字表示存在，在使用exists关键字时，内层查询语句不返回查询的记录。

而返回一个真假值。ture或false

当返回ture时，外层查询语句将进行查询；当返回值为false时，外层查询语句不进行查询

select * from employee where exists (select dept_name from department where dept_id=203);

create table test1(

id INT PRIMARY KEY AUTO_INCREMENT,

name varchar(20),

salary INT DEFAULT 1000

);

insert into test1 (name) values("A1"),

("A2"),

("A3"),

("A4");

create table emp(

id INT,

name varchar(20),

index index_name (name)

);

创建全文索引示例：

create table emp2(

id INT,

name varchar(20),

resume varchar(30),

FULLTEXT index index_resume(resume)

);

创建多列索引示例：

create table emp3(

id INT,

name varchar(20),

resume varchar(30),

index index_name_resume (name,resume)

);

create table t1(id int,name varchar(20));

delimiter $$

create procedure autoinsert()

BEGIN

declare i int default 1;

while(i<5000)do

insert into t1 values(i,"yuan");

set i=i+1;

end while;

END$$

call autoinsert();调用函数

create index index_name on t1(id);添加索引

drop index index_name on t1;删除索引

mysql事务的处理：

start transaction 开始事务

rollback 回滚事务

commit 提交事务

savepoint保留点，事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)

ps：insert into table values(1,"XXX");

savepoint insert1(为保留的节点命名)

rollback to insert1(返回节点,后面的操作全没有)