【MYSQL】SQL语法增删查改易懂代码
DDL:Data Defination language 数据库定义语句 carate\drop\primary
ALTER DATABASE用于更改数据库的全局特性。这些特性储存在数据库目录中的db.opt文件中。要使用ALTER DATABASE,您需要获得数据库ALTER权限。
CREATE DATABASE IF NOT EXISTS MYDB 创建数据库
MYDB 表名
是否存在表
IF NOT EXISTS
创建表
DROP TABLE person
CREATE TABLE person{//创建表内容id int(11) PRIMARY key auto_increment,name VARCHAR(10),age int,adderss VARCHAR(30),isdeleted TINYINT(1),}CREATE INDEX part_of_name ON customer (name(10))DROP INDEX index_name on presonDROP TABLE personRENAME TABLE person To person2
外键
PRIMARY key auto_increment
在一个语句中取消多个列:
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
数据是否删除
isdeleted TINYINT(1)
此处展示的语句用于创建一个索引,索引使用列名称的前10个字符。
CREATE INDEX part_of_name ON customer (name(10));
删除索引
DROP INDEX index_name on preson
删除表
DROP TABLE person
CHANGE语法仍然要求旧的和新的列名称,即使旧的和新的列名称是一样的
RENAME TABLE person To person2
列加外键
ENABLE KEYS
删除列
ALTER TABLE person2 DROP COLUMN isdeleted
修改类型而不是名称
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
存储引擎 |
允许的索引类型 |
MyISAM |
BTREE |
InnoDB |
BTREE |
MEMORY/HEAP |
HASH, BTREE |
DROP DATABASE用于取消数据库中的所用表格和取消数据库。使用此语句时要非常小心!如果要使用DROP DATABASE,您需要获得数据库DROP权限。
IF EXISTS用于防止当数据库不存在时发生错误。
也可以使用DROP SCHEMA。
如果您对一个带有符号链接的数据库使用DROP DATABASE,则链接和原数据库都被取消。
DROP DATABASE会返回已被取消的表的数目。此数目相当于被取消的.frm文件的数目。
在正常操作中MySQL自身会创建出一些文件和目录。DROP DATABASE语句会从给定的数据库目录中取消这些文件和目录:
.BAK |
.DAT |
.HSH |
.MRG |
.MYD |
.ISD |
.MYI |
.db |
.frm |
只要两个数据库位于同一文件系统中,您还可以对表进行重命名,把表从一个数据库中移动到另一个数据库中:
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
INSERT插入语法
INSERT用于向一个已有的表中插入新行。INSERT...VALUES和INSERT...SET形式的语句根据明确指定的值插入行。INSERT...SELECT形式的语句插入从其它表中选出的行。
-- 插入语句:insert ... values 格式-- 1、全量字段插入insert into person(id,name,age,address,isDeleted) values(10,'zhangsan',13,'北京市昌平区',0)-- 2、全量字段插入(设置字段的默认值)insert into person(id,name,age,address,isDeleted) values(DEFAULT(id),'zhangsan',13,'北京市昌平区',0)-- 3、全量字段插入(设置字段的默认值)insert into person(id,name,age,address,isDeleted) values(DEFAULT,'zhangsan',13,'北京市昌平区',0)-- 4、唯一且自增的主键不插入insert into person(name,age,address,isDeleted) values('zhangsan',13,'北京市昌平区',0)-- 5、是否将插入语句变为更新语句 ON DUPLICATE KEY UPDATEinsert into person(id,name,age,address,isDeleted) values(10,'zhangsan',13,'北京市昌平区',0)ON DUPLICATE KEY UPDATE id=30,name = 'lisi'-- 6、一条语句插入多行数据insert into person(name,age,address,isDeleted) values('刘备',45,'荆州',0),('关羽',43,'荆州',0),('张飞',42,'荆州',0)-- 7、插入语句 insert......setINSERT into person set name='曹操',age = 50,address = '曹魏',isDeleted = 0INSERT into person set name='夏侯渊',age = 46,isDeleted = 0-- 8、插入语句 insert ...... selectinsert into person(name,age,address,isDeleted)SELECT name,age,address,isDeleted from person2-- 其他的插入INSERT INTO person () VALUES();-- 表达式插入insert into person(name,age,address) VALUES('袁绍',30,CONCAT(name,'基地'))insert into person(name,age,address) VALUES(60,name-20,'基地')
UPDATE语法
UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句,则更新所有的行。如果指定了ORDER BY子句,则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值,限制可以被更新的行的数目。
update person set name='孙策',age = 30 where id = 39update person set address='江东' where id = 39 or name = '孙策'update person set address='江东' where id = 39 or name = '孙策'update person set address='江东' where id in(39,42)update person set address='江东' where name = '孙策' or name = '孙全'update person set address='江东' where name like '孙%'update person set age=age+1 where name like '孙%'
DELETE语法
如果您编写的DELETE语句中没有WHERE子句,则所有的行都被删除。当您不想知道被删除的行的数目时,有一个更快的方法,即使用TRUNCATE TABLE。
单表语法:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
多表语法:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*] ...]
FROM table_references
[WHERE where_definition]
-- 删除DELETE FROM person WHERE ID <30 ORDER BY ID DESC LIMIT 2
简单查询
SELECT CONCAT(name,'-',address) FROM person SELECT CONCAT(name,'-',address) AS name_addressFROM person ORDER BY name_address;SELECT CONCAT(name,'-',address) name_addressFROM person ORDER BY name_address;SELECT name, age FROM personSELECT id,name,age,address,isDeleted from person;select * from personSELECT * from person where name = '吕布'select * from person WHERE age = 30SELECT * from person where address = '江东'-- 函数查询-- 数量select count(*) from personselect count(id) from personselect count(name) from personselect count(1) from person-- 数学运算符查询select * from person where age > 20select * from person where age > 20 and age (select avg(salary) from salary)select * from person where id in (select personId from salary where salary > (select avg(salary) from salary))-- 查询年纪在20-40之间的人的工资select id from person where age BETWEEN 20 and 40select salary from salary where personId in (select id from person where age BETWEEN 20 and 40)-- 蜀国人的平均工资select id from person where country = '蜀'select avg(salary) from salary where personid in (select id from person where country = '蜀')