wns9778.com_威尼斯wns.9778官网

热门关键词: wns9778.com,威尼斯wns.9778官网
wns9778.com > 计算机教程 > MySQL基础二,mysql基础

原标题:MySQL基础二,mysql基础

浏览次数:133 时间:2020-03-12

/*********************************************************/ function:SQL MSSQL TECHNOLOGY ARTICLE file :SQL-MSSQL.TXT author :chinayaosir QQ:44633197 Tools :MSSQL QUERY ANALYSIS date :4/01/2010 blog : note :禁止其它网站转载此文章 /*********************************************************/ 目录清单CONTEXT LIST /*********************************************************/ 1.数据库DataBase 1.1数据库建立/删除create/drop database 1.2数据库备份与恢复backup/restore database /*********************************************************/ 2.数据查询DATA QUERY LANGUAGE 2.1选择查询Select Query 2.2聚集查询Aggregate Query 2.3子查询 Sub Query 2.4连接查询Table Joins 2.5汇总查询Group Query /*********************************************************/ 3.数据修改DATA MODIFY LANGUAGE 3.1插入数据Insert 3.2修改数据Update 3.3删除数据Delete /*********************************************************/ 4.数据定义DATA DEFINE LANGUAGE 4.1表Table 4.2列Column 4.3序列Identity 4.4约束Constraints 4.5索引Index 4.6视图view 4.7权限Privilege /*********************************************************/ 5.数据库函数Functions 5.1转换函数Data Convert Functions 5.2聚集函数Aggregate Functions 5.3字符函数char Functions 5.4日期函数Date Functions 5.5数学函数Math Functions 5.6分析函数Analytical Functions /*********************************************************/ 6.数据库脚本Script 6.1数据类型Data Types 6.2脚本语法Statements 6.3脚本游标Cursor 6.4存储过程Procedure 6.5存储函数Function 6.6触发器Trigger 6.7事务Transaction 6.8其它Other /*********************************************************/ SQL明细 SQL DETAIL /**********************************************************/ 1.数据库DataBase 1.1数据库建立/删除create/drop database 1.2备份与恢复backup/restore database /**********************************************************/ 1.1数据库建立/删除create/drop database 1.1.1.建立数据库 语法:create database 数据库名 [其它参数] 代码: //建立数据库 hr create database hr 1.1.2.删除数据库。 语法:drop database 数据库名 代码: //删除数据库hr drop database hr //如果存在hr数据库,则删除数据库hr IF DB_ID('hr') IS NOT

MySQL基础二,mysql基础

NULL DROP DATABASE TestDB

1.2备份与恢复backup/restore database 1.2.1.添加备份设备 语法:sp_addumpdevice keyword devicename devicepath 代码: //添加备份设备为本地硬盘 sp_addumpdevice 'disk', 'localbackup', 'e:databasebackuplocalbak.bak' //备份到网络硬盘 sp_addumpdevice 'disk', 'netbackup', '\computer1databasebackupnetbak.bak' //备份到磁带 sp_addumpdevice 'tape', 'tapebackup', '\.tape1bak' //备份到命名管道 sp_addumpdevice 'pipe', 'pipebackup', 'e:databasebackuppipebak' 1.2.2.备份数据库 语法:backup database databasename to devicename| disk=backupnamepath 代码: //备份数据库到备份设备 backup database pubs to localbackup //备份数据库到指定路径下面的指定文件 backup database pubs to disk='e:databasebackuppubsbak.bak' 1.2.3.恢复数据库 语法:restore database databasename from devicename| disk=backupnamepath 代码: //从备份设备中恢复数据库 restore database pubs from localbackup //从备份文件中恢复数据库 /**********************************************************/ 2.数据查询DATA QUERY LANGUAGE 2.1选择查询Select Query 2.2子查询 Sub

视图

视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集,并可以将其当作表来使用。

图片 1SELECT * FROM ( SELECT nid, NAME FROM tb1 WHERE nid > 2 ) AS A WHERE A. NAME > 'alex'; 临时表搜索

1、创建视图

图片 2--格式:CREATE VIEW 视图名称 AS SQL语句 CREATE VIEW v1 AS SELET nid, name FROM A WHERE nid > 4 View Code

2、删除视图

图片 3--格式:DROP VIEW 视图名称 DROP VIEW v1 View Code

3、修改视图

图片 4-- 格式:ALTER VIEW 视图名称 AS SQL语句 ALTER VIEW v1 AS SELET A.nid, B. NAME FROM A LEFT JOIN B ON A.id = B.nid LEFT JOIN C ON A.id = C.nid WHERE A.id > 2 AND C.nid < 5 View Code

4、使用视图

使用视图时,将其当作表进行操作即可,由于视图是虚拟表,所以无法使用其对真实表进行创建、更新和删除操作,仅能做查询用。

图片 5select * from v1 View Code

Query 2.3连接查询Table Joins 2.4汇总查询Group Query

2.1选择查询Select Query 语法: select [top n][/all]/[distinct] [*] / [columnlist...] [columnlist as alias...] [const/sql/function expression] from (tablelist,...) [as alias] [where search expression...] [group by groupnamelist ....] [having search-expression...] [order by sort-expression...] //select选项说明: top n:只显示第一条到n条记录 //重复与不重复记录 all:表示包含重复的记录 distinct:表示去掉重复的记录 //所有字段与选中字段和字段别名 *:表示所有的列名 columnlist:表示字段列表 columnlist as alias:表示字段的别名 //其它字段 const-expression:常量表达式(如数字/字符串/日期/时间常量) sql-expression:常见的sql语句的加减乘除表达式运算字段 function expression:数据库函数和自定义函数字段 //测试条件 比较测试条件(=,,,,=,=) 范围测试条件(betweeen 下限值 and 上限值) 成员测试条件(in,not in) 存在测试条件(exists,not exists) 匹配测试条件(like) 限定测试条件(any,all) 空值测试条件(is null) //复合搜索条件(and, or,not,()) and:逻辑与运算 and:逻辑或运算 not:逻辑非运算 ():可改变优先级的运算符 //子句说明 select子句:指出检索的数据项 from 子句:指出检索的数据表 where 子句:指出检索的数据条件 group by子句:指出检索的数据进行汇总 having子句:指出检索的数据进行汇总之前的条件 order by子句:指出检索的数据条件进行排序 代码: //所有字段方式显示orders全部记录 select * from orders //按字段显示全部记录 select order_num,order_date,amount from orders //按字段显示全部记录,但除掉重复的记录 select order_num,order_date,amount from orders //用sql-expression乘运算计算列 select amount,amount*0.08 as discount_amt from orders //用自定义函数计算指定列 select order_num,order_date,amount,f_amt_to_chn(amount) as 金额 from orders select选项太多,代码例子就省略... ----------------------------------------------------------- 2.2子查询 Sub Query 语法:select ... from tablename where / having column 测试条件 (Sub Query) //测试条件 比较测试条件(=,,,,=,=) 范围测试条件(betweeen 下限值 and 上限值) 成员测试条件(in,not in) 存在测试条件(exists,not exists) 匹配测试条件(like) 限定测试条件(any,all) 空值测试条件(is null) 代码: //列出没有完成销售目标10%的销售人员清单[测试] select name from salesreps where quota (0.1 * select sum(target) from offices)) //列出公司的销售目标超过各个销售人员定额总和的销售点[测试] select city from offices where target (select sum(quota) from salesreps where rep_office=office) //列出超过销售目标的销售点的业务人员[in测试] select name from salesreps where office in (select office from offies where sales target) //列出订单大于2500元的产品名称[exists测试] select description from products where exists ( select * from orders where product=prodct_id and amount 2500.00 ) //列出完成销售目标10%的销售人员清单[any测试] select name from salesreps where (0.1* quota) any(select amount from orders where

触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时,可以使用触发器,触发器用于定制用户对表的行进行【增/删/改】前后的行为。

1、创建基本语法

图片 6# 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN ... END # 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN ... END # 删除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN ... END # 删除后 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN ... END # 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN ... END # 更新后 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN ... END View Code 图片 7delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. NAME == 'alex' THEN INSERT INTO tb2 (NAME) VALUES ('aa') END END// delimiter ; 插入前触发器 图片 8delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. num = 666 THEN INSERT INTO tb2 (NAME) VALUES ('666'), ('666') ; ELSEIF NEW. num = 555 THEN INSERT INTO tb2 (NAME) VALUES ('555'), ('555') ; END IF; END// delimiter ; 插入后触发器

特别的:NEW表示即将插入的数据行,OLD表示即将删除的数据行。

2、删除触发器

图片 9DROP TRIGGER tri_after_insert_tb1; View Code

3、使用触发器

触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的。

图片 10insert into tb1(num) values(666) View Code

rep=empl_num)

2.3连接查询Table Joins 多表连接类型可分为三类(内/外/交叉连接) 主从表或者父子表进行多表连接多以主键和外键进行关联 Outer joins(LEFT OUTER, RIGHT OUTER, and FULL OUTER joins) left outer join:查询的结果以左边表行数为准 right outer join:查询的结果以右边表行数为准 2.3.1.内连接inner join 功能: 语法: SELECT select_list FROM table_1 [INNER] JOIN table_2 ON join_condition_1 [[INNER] JOIN table_3 ON join_condition_2]... 代码: //没有where子句的内连接 SELECT * FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID //有where子句的内连接 SELECT p.ProductID, s.SupplierID, p.ProductName, s.CompanyName FROM Products p INNER JOIN Suppliers s ON p.SupplierID =

存储过程

存储过程是一个SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行。

1、创建存储过程

图片 11-- 创建存储过程 delimiter // create procedure p1() BEGIN select * from t1; END// delimiter ; -- 执行存储过程 call p1() 无参数存储过程

对于存储过程,可以接收参数,其参数有三类:

  • in          仅用于传入参数用
  • out        仅用于返回值用
  • inout     既可以传入又可以当作返回值

图片 12-- 创建存储过程 delimiter \ create procedure p1( in i1 int, in i2 int, inout i3 int, out r1 int ) BEGIN DECLARE temp1 int; DECLARE temp2 int default 0; set temp1 = 1; set r1 = i1 i2 temp1 temp2; set i3 = i3

  • 100; end\ delimiter ; -- 执行存储过程 DECLARE @t1 INT default 3; DECLARE @t2 INT; CALL p1 (1, 2 ,@t1, @t2); SELECT @t1,@t2; 有参数存储过程

2、删除存储过程

图片 13drop procedure proc_name; View Code

3、执行存储过程

图片 14-- 无参数 call proc_name() -- 有参数,全in call proc_name(1,2) -- 有参数,有in,out,inout DECLARE @t1 INT; DECLARE @t2 INT default 3; call proc_name(1,2,@t1,@t2) View Code 图片 15#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行存储过程 cursor.callproc('p1', args=(1, 22, 3, 4)) # 获取执行完存储的参数 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = cursor.fetchall() conn.commit() cursor.close() conn.close() print(result) pymysql执行存储过程

s.SupplierID WHERE p.ProductID 4

2.3.2.外连接outer join 功能:包括三种连接LEFT OUTER, RIGHT OUTER, and FULL OUTER joins left outer :查询的结果以左边表行数为准 right outer :查询的结果以右边表行数为准 语法:select ... from table1 [left/right/full outer join ]table2 where ... 代码: //以Customers表行数为标准去连接Orders表 SELECT c.CustomerID, CompanyName FROM Customers c LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID

函数

MySQL中提供了许多内置函数,例如:

图片 16 CHAR_LENGTH(str) 返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。 对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 CONCAT(str1,str2,...) 字符串拼接 如有任何一个参数为NULL ,则返回值为 NULL。 CONCAT_WS(separator,str1,str2,...) 字符串拼接(自定义连接符) CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。 CONV(N,from_base,to_base) 进制转换 例如: SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示 FORMAT(X,D) 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点,或不含小数部分。 例如: SELECT FORMAT(12332.1,4); 结果为: '12,332.1000' INSERT(str,pos,len,newstr) 在str的指定位置插入字符串 pos:要替换位置其实位置 len:替换的长度 newstr:新字符串 特别的: 如果pos超过原字符串长度,则返回原字符串 如果len超过原字符串长度,则由新字符串完全替换 INSTR(str,substr) 返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len) 返回字符串str 从开始的len位置的子序列字符。 LOWER(str) 变小写 UPPER(str) 变大写 LTRIM(str) 返回字符串 str ,其引导空格字符被删除。 RTRIM(str) 返回字符串 str ,结尾空格字符被删去。 SUBSTRING(str,pos,len) 获取字符串子序列 LOCATE(substr,str,pos) 获取子序列索引位置 REPEAT(str,count) 返回一个由重复的字符串str 组成的字符串,字符串str的数目等于count 。 若 count <= 0,则返回一个空字符串。 若str 或 count 为 NULL,则返回 NULL 。 REPLACE(str,from_str,to_str) 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 REVERSE(str) 返回字符串 str ,顺序和字符顺序相反。 RIGHT(str,len) 从字符串str 开始,返回从后边开始len个字符组成的子序列 SPACE(N) 返回一个由N空格组成的字符串。 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 返回字符串 str , 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项,在未指定情况下,可删除空格。 mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx' 部分内置函数

更多函数:猛击这里 OR 猛击这里

1、自定义函数

图片 17delimiter \ create function f1( i1 int, i2 int) returns int BEGIN declare num int; set num = i1 i2; return(num); END \ delimiter ; View Code

2、删除函数

图片 18drop function func_name; View Code

3、执行函数

图片 19# 获取返回值 declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; # 在查询中使用 select f1(11,nid) ,name from tb2; View Code

WHERE o.CustomerID IS NULL

2.3.3.交叉连接cross join 功能:以主从表或者父子表之间的主键进行连接,最终以笛卡尔乘积运算的结果 语法:select ... from table1 cross join table2 where ... 代码: //显示结果以表1行数*表2行数 假设Departments为4行记录 假设Jobs为3行记录 下面的显示结果为4*3=12行记录 SELECT deptname,jobdesc FROM Departments CROSS JOIN Jobs //用关键字匹配的交叉连接 oc_head/oc_detail是主从表 oc_head(主键oc_number) oc_detail(主键oc_number,item_number,ship_date) SELECT h.customerid,d.item_number,d.ship_date from oc_head as h CROSS JOIN

事务

事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。

图片 20delimiter \ create PROCEDURE p1( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; DELETE from tb1; insert into tb2(name)values('seven'); COMMIT; -- SUCCESS set p_return_code = 0; END\ delimiter ; 支持事务的存储过程 图片 21DECLARE @i TINYINT; call p1(@i); select @i; 执行存储过程

oc_detail as d where h.oc_number=d.oc_number

2.4汇总查询Group Query //汇总查询相当于会计报表中的小计汇总的功能 语法: select ... from tablename group by column-name [having search expression] 代码: //求出每名销售人员的销售金额 select rep,sum(amount) from orders group by rep //每个销售点分配了多少销售人员 select rep_office,count(*) from salesreps group by rep_office //计算每名销售人员的每个客户和订单金额 select cust,rep,sum(amount) from orders group by cust,rep //Having子句应用 select rep,avg(amount) from orders having sum(quota) 3000.00 /**********************************************************/ 3.数据修改DATA MODIFY LANGUAGE 3.1插入数据Insert 3.2修改数据Update

索引

索引,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即可。

MySQL中常见索引有:

  • 普通索引
  • 唯一索引
  • 主键索引
  • 组合索引

1、普通索引

普通索引仅有一个功能:加速查询

图片 22create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) 创建表 索引 图片 23create index index_name on table_name(column_name) 创建索引 图片 24drop index_name on table_name; 删除索引 图片 25show index from table_name; 查看索引

注意:对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。

图片 26create index ix_extra on in1(extra(32)); View Code

2、唯一索引

唯一索引有两个功能:加速查询 和 唯一约束(可含null)

图片 27create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, unique ix_name (name) ) 创建表 唯一索引 图片 28create unique index 索引名 on 表名(列名) 创建唯一索引 图片 29drop unique index 索引名 on 表名 删除唯一索引

3、主键索引

主键有两个功能:加速查询 和 唯一约束(不可含null)

图片 30create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) OR create table in1( nid int not null auto_increment, name varchar(32) not null, email varchar(64) not null, extra text, primary key(ni1), index ix_name (name) ) 创建表 创建主键 图片 31alter table 表名 add primary key(列名); 创建主键 图片 32alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key; 删除主键

4、组合索引

组合索引是将n个列组合成一个索引

其应用场景为:频繁的同时使用n列来进行查询,如:where n1 = 'alex' and n2 = 666。

图片 33create table in3( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text ) 创建表 图片 34create index ix_name_email on in3(name,email); 创建组合索引

如上创建组合索引之后,查询:

  • name and email  -- 使用索引
  • name                 -- 使用索引
  • email                 -- 不使用索引

注意:对于同时搜索n个条件时,组合索引的性能好于多个单一索引合并。

3.3删除数据Delete

3.1插入数据Insert 3.1.1.单行插入 语法:insert into tablename[columnlist,...] values(valuelist,...); 代码: //不省略字段清单 insert into salesreps(name,age,empl_no,sales,title,hire_date,rep_office) values('jack toms',36,111,0.00,'sales mgr','10-05-2010',13) //省略字段清单 insert into salesreps values('jack toms',36,111,0.00,'sales mgr','10-05-2010',13) 3.1.2.多行插入 语法:insert into tablename[(columnlist,...)] values(valuelist,...) select Query; 代码: //把一批数据批量插入到一个备份表中 insert into history_order(order_num,order_date,amount) select

其他 

1、条件语句

图片 35delimiter \ CREATE PROCEDURE proc_if () BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF; END\ delimiter ; if条件语句

2、循环语句

图片 36delimiter \ CREATE PROCEDURE proc_while () BEGIN DECLARE num INT ; SET num = 0 ; WHILE num < 10 DO SELECT num ; SET num = num 1 ; END WHILE ; END\ delimiter ; while循环 图片 37delimiter \ CREATE PROCEDURE proc_repeat () BEGIN DECLARE i INT ; SET i = 0 ; repeat select i; set i = i 1; until i >= 5 end repeat; END\ delimiter ; repeat循环 图片 38delimiter \ CREATE PROCEDURE proc_loop () BEGIN declare i int default 0; loop_label: loop select i; set i=i 1; if i>=5 then leave loop_label; end if; end loop; END\ delimiter ; loop

3、动态执行SQL语句

图片 39delimiter \ DROP PROCEDURE IF EXISTS proc_sql \ CREATE PROCEDURE proc_sql () BEGIN declare p1 int; set p1 = 11; set @p1 = p1; PREPARE prod FROM 'select * from tb2 where nid > ?'; EXECUTE prod USING @p1; DEALLOCATE prepare prod; END\ delimiter ; 动态执行SQL

 

http://www.bkjia.com/Mysql/1153668.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1153668.htmlTechArticleMySQL基础二,mysql基础 视图 视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需...

order_num,order_date,amount from orders where order_date '01/01/2000'

3.2修改数据Update 语法:update tablename set (cloumn=expression...) [where ...] [SubQuery..] 代码: //更新所有记录 update salesreps set quota=1.05 * quota //按条件更新表记录 update salesreps set quota=1.08 * quota where area='china' //按子查询更新表记录 update customers set cust_rep=105 where cust_rep in ( selct empl_num from salesreps where

sales (0.8 * quota) )

3.3删除数据Delete 语法1:delete from tablename [where ...] 代码: //所有删除记录 delete from orders 语法2:truncate table tablename 代码2: //所有删除记录 truncate table orders //按条件删除记录 delete from orders where order_date '01/01/2000' /**********************************************************/ 4.数据定义DATA DEFINE LANGUAGE 4.1表Table 4.2列Column 4.3序列Indentity 4.4约束Constraints 4.5索引Index 4.6视图view 4.7权限Privilege /**********************************************************/ 4.1表Table 4.1.1.建立表 语法: create table 表名( 列名 数据类型 [长度] , 列名... ) 代码: //建立公司部门表 create table tb_basic_dept( id int not null, name varchar(20) , chair varchar(20) ) 4.1.2.删除表 语法: drop table 表名 代码: //删除部门表 drop table tb_basic_dept ----------------------------------------------------------- 4.2列Column 4.2.1.列添加 语法: alter table 表名 add 列名 数据类型 [长度] , 列名... 代码: alter table tb_basic_dept add remark varchar(50) 4.2.2.列删除 语法:alter table 表名 drop column 列名 代码: alter table tb_basic_dept drop column remark 4.2.3.列修改 语法:alter table 表名 alter column 列名 数据类型 [长度] [null | not null] 代码: //修改工资列为dec(8,2) alter

table tb_hr_gz alter column gz dec(8,2) null

4.3序列Identity //特别要求 IDENTITY字段数据类型只能是(int, bigint, smallint, tinyint, decimal, or numeric(x,0)) IDENTITY字段必须是not null约束 4.3.1Identity 语法: IDENTITY(data_type [, seed, increment]) AS column_name, 代码: //使用Identity CREATE TABLE MyTable ( key_col int NOT NULL IDENTITY (1,1), abc char(1) NOT NULL ) INSERT INTO MyTable VALUES ('a') INSERT INTO MyTable VALUES ('b') INSERT INTO MyTable VALUES ('c') ----------------------------------------------------------- 4.4约束Constraints 4.4.1缺省约束(default) 4.4.2非空约束(not null) 4.4.3规则约束(rule) 4.4.4检查约束(check) 4.4.5唯一约束(unique) 4.4.6主键约束(primary key) 4.4.7外键约束(foreign key) 4.4.8商业规则(business rule) 以下面两个表为例进行演示 create table tb_hr_bm( bm varchar(20) not null , remark varchar(100) default '' ) create table tb_hr_gz( id int not null, name varchar(30) not null, hrid char(18) null, workage int null , bm varchar(20) null, gz real null, remark varchar(100) null ) hrid=身份证号码 workage=工作年数 gz=工资金额 ----------------------------------------------------------- 4.4.1缺省约束(default) 语法:CREATE DEFAULT default_name AS expression

代码:CREATE DEFAULT zip_default AS 94710

4.4.2非空约束(not null) //表的主键和其它必填字段必须为not null. 语法:create table (column-name datatype not null... ) 代码:create table

tb_hr_gz(id int not null,...)

4.4.3规则约束(rule) 语法:CREATE RULE rulename AS condition 代码: //邮编号码6位100000-999999 //建立一个自定义zip类型 CREATE TYPE zip FROM CHAR(6) NOT NULL //建立一个规则约束 CREATE RULE zip_rule AS @number 100000 and @number 999999 //绑定规则约束到zip类型 EXEC sp_bindrule zip_rule, 'zip' //应用自定义zip类型 2 CREATE TABLE address( city

CHAR(25) NOT NULL, zip_code ZIP, street CHAR(30) NULL )

4.4.4检查约束(建立/删除) //检查约束建立 语法: alter table name add constraint 检查约束名 check取值范围表达式 代码: //工资添加取值范围0 ~ 1000000 方法1: create table tb_hr_gz( gz real default 0.0 check(gz =0 and gz =1000000), ... ) 方法2: alter table tb_hr_gz add constraint tb_hr_gz_ck check(gz =0 and gz =1000000) //检查约束删除 语法: alter table name drop constraint 检查约束名 代码: //删除工资的检查约束 alter

table tb_hr_gz drop constraint tb_hr_gz_ck

4.4.5唯一约束 4.4.5.1.唯一约束添加 语法: alter table name add constraint 唯一约束名 unique列名 代码: //列如身份证号码是唯一的! alter table tb_hr_gz Add constraint tb_hr_gz_uk unique(hrid) 4.4.5.2.唯一约束删除 语法: alter table name drop constraint 唯一约束名

代码: alter table tb_hr_gz drop constraint tb_hr_gz_uk

4.4.6主键约束 4.4.6.1主键约束添加 语法: alter table table_name add constraint 主键名称 Primary Key 列名 代码: create table tb_hr_bm( bm varchar(20) not null , remark varchar(100) default '' ) alter table tb_hr_bm add constraint tb_hr_bm_pk Primary Key (bm) 4.4.6.2主键约束删除 语法: alter table table_name drop constraint

主键名称 代码: alter table table_name drop constraint tb_hr_bm_pk

4.4.7外键约束 4.4.7.1外键约束添加 语法: alter table 表名 add constraint 外键名 foreign key(列名) references 参考表名列名 ON UPDATE|ON DELETE(RESTRICT|CASCADE|SET NULL|SET DEFAULT) //补充说明 常用选项是下面3项: ON UPDATE SET NULL //级联更新 ON DELETE CASCADE //级联删除 ON DELETE SET NULL //级联置空 ON UPDATE(RESTRICT|CASCADE|SET NULL|SET DEFAULT) 表示父表更新后,子表的行为 ON DELETE(RESTRICT|CASCADE|SET NULL|SET DEFAULT) 表示父表删除后,子表的行为 RESTRICT 限制功能:父表一行记录不能更新/删除,当子表有一条记录以上时 CASCADE 级联功能:父表一行记录记录更新/删除删除,子表对应所有的记录自动更新/删除 SET NULL 置空功能:父表一行记录记录更新/删除删除,子表对应所有的记录自动为空 SET DEFAULT 默认值功能:父表一行记录记录更新/删除删除,子表对应所有的记录自动写入默认值 代码: 建立外键的主要代码 alter table tb_hr_personl_info add constraint tb_hr_personl_info__bm_fk foreign key(bm) references tb_hr_bm (bm) on update cascade on delete cascade //建立参考表部门 create table tb_hr_bm ( bm varchar(20) not null , remark varchar(100) default '' ) alter table tb_hr_bm add constraint tb_hr_bm_pk Primary Key (bm) //建立个人信息表 use hr create table tb_hr_personl_info ( userid int not null , username varchar(20) null, bm varchar(20) null ) /*为此表添加主键约束*/ alter table tb_hr_personl_info add constraint tb_hr_personl_info_pk Primary Key (userid) /*为个人信息表添加外键约束*/ alter table tb_hr_personl_info add constraint tb_hr_personl_info__bm_fk foreign key(bm) references tb_hr_bm (bm) on update cascade on delete cascade ----------------------------------------------------------- 4.4.7.2外键约束删除 语法: alter table 表名 drop constraint 外键名 代码: //删除tb_hr_personl_info表的外键 alter table tb_hr_personl_info

drop constraint tb_hr_personl_info__bm_fk;

4.4.8商业规则(business rule) //用触发器或者存储过程来实现 ----------------------------------------------------------- 4.5索引Index //4.5.1建立索引 语法: create index 索引名 on 表名 列名清表 代码: create index tb_hr_personl_info_ix on tb_hr_personl_info (userid) //4.5.2删除索引 语法: drop index 表名.索引名 代码: //删除索引名tb_hr_personl_info_ix drop index tb_hr_personl_info.tb_hr_personl_info_ix ----------------------------------------------------------- 4.6视图view 4.6.1视图view的概念:

本文由wns9778.com发布于计算机教程,转载请注明出处:MySQL基础二,mysql基础

关键词: wns9778.com

上一篇:【经验分享】SQL 中的统计应用

下一篇:没有了