CREATE TABLE my_contacts
( last_name VARCHAR(30), first_name VARCHAR(30), email VARCHAR(50), gender CHAR(1), birthday DATE, profession VARCHAR(50), location VARCHAR(50), status VARCHAR(20), interests VARCHAR(100), seeking VARCHAR(100));
1. IS NULL
2. LIKE, 通配符:%任意数量字符;_一个字符
3. BETWEEN AND(等价于<=,>=)
4. NOT: 置于每个查询条件前,还有NOT IN/ NOT NULL
5. IN
DROP TABLE IF EXISTS drink_info; CREATE TABLE drink_info ( drink_name VARCHAR(20) NOT NULL, cost DEC(2,1) NOT NULL, carbs DEC(3,1) NOT NULL, color VARCHAR(10) NOT NULL, ice CHAR(1) NOT NULL, calories int NOT NULL );
INSERT INTO drink_info VALUES ('Blackthorn',2,8.4,'yellow','y',33), ('Blue Moon',2.5,3.2,'blue','y',12), ('Oh My Gosh',3.5,8.6,'orahge','y',35), ('Lime Fizz',2.5,5.4,'green','y',171), ('Indian Summer',2.8,7.2,'brown','N',30), ('kiss',5.5,42.5,'burbe','y',171); 设计/创建 数据库表时,对象化事物,按需求找出原子操作。 数据库表规范化 - 1NF,第一范式 主键 SHOW CREATE TABLE 表名,用于拿到create table时的代sql
SHOW CREATE TABLE my_contacts;
SHOW COLUMNS FROM my_contacts;SHOW CREATE DATABASE gregs_list;SHOW INDEX FROM my_contacts;CREATE TABLE onetable
( id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(20) NOT NULL, second_name VARCHAR(20) NOT NULL,PRIMARY KEY(id));
INSERT INTO onetable
VALUES(NULL,'chris','chen');INSERT INTO onetable
(first_name,second_name)VALUES ('BOB', 'BARY');INSERT INTO onetable
VALUES (99,'BOB', 'BARY');
AUTO_INCREMENT会自动忽略NULL关键字
设计表时,遵循1NF(第一范式):1. 数据原子性;2. 每行唯一标识(primary key)
ALTER TABLE my_contacts ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id);
ALTER TABLE my_contacts ADD COLUMN phone_number CHAR(10); 默认将phone_number列添加到表的最后。
DROP TABLE IF EXISTS projekts; CREATE TABLE projekts ( number INT, descriptionofproj VARCHAR(50), contractoronjob VARCHAR(10) ); INSERT INTO projekts (descriptionofproj, contractoronjob) VALUES ('outside house painting','Murphy'), ('kitchen remodel','Valdez'), ('wood floor installation','keller');
使用ALTER的Change,Modify,Add对原表进行修改。 1. 修改表名:rename to
ALTER TABLE projekts RENAME TO project_list;
2. 改变列名及类型
ALTER TABLE project_list CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (proj_id);
3. 同时改变多列的名称和类型
ALTER TABLE project_list CHANGE COLUMN descriptionofproj proj_desc VARCHAR(100), CHANGE COLUMN contractoronjob con_name VARCHAR(30); 4. 只改变列的类型,使用Modify
ALTER TABLE project_list MODIFY proj_desc VARCHAR(120);
5. 同时增加多列
ALTER TABLE project_list ADD COLUMN phone VARCHAR(20) NOT NULL, ADD COLUMN start_date DATE NOT NULL, ADD COLUMN price DEC(10,2) NOT NULL;
6. 删除某列
ALTER TABLE project_list DROP COLUMN start_date; 7. ALTER练习
DROP TABLE IF EXISTS hooptie; CREATE TABLE hooptie ( color VARCHAR(10), year CHAR(4), make VARCHAR(10), mo VARCHAR(10), howmuch DEC(9,3) ); INSERT INTO hooptie VALUES ('silver','1998','Porsche','Boxter',17992.540), ('red','2002','Cadillac','Escalade',40215.9); INSERT INTO hooptie (year,make,mo,howmuch) VALUES ('2000','Jaguar','XJ',15995);
ALTER TABLE hooptie ADD COLUMN car_id INT NOT NULL AUTO_INCREMENT FIRST, ADD COLUMN VIN VARCHAR(20) NOT NULL AFTER car_id, ADD PRIMARY KEY(car_id);
UPDATE hooptieSET VIN = 'RNKLK66N33G213481' WHERE car_id = 1;
UPDATE hooptie
SET VIN = 'SAEDA44B175BO4113'WHERE car_id = 2;UPDATE hooptie
SET VIN = '3GYEK63NT2G280668'WHERE car_id = 3;
DROP TABLE IF EXISTS cookie_sales; CREATE TABLE cookie_sales ( ID INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(15) NOT NULL, sales DEC(4,2), sale_date DATE, PRIMARY KEY(ID) ); INSERT INTO cookie_sales (first_name, sales, sale_date) VALUES ('Lindsay', 32.02, '2007-03-06'), ('Paris', 26.53, '2007-03-06'), ('Britney', 11.25, '2007-03-06'), ('Nicole', 18.96, '2007-03-06'), ('Lindsay', 9.16, '2007-03-07'); INSERT INTO cookie_sales (first_name, sales, sale_date) VALUES ('Britney', 11.75, '2007-03-06');
SELECT SUM(sales) FROM cookie_sales WHERE first_name = 'Britney';
SELECT first_name, SUM(sales) FROM cookie_sales GROUP BY first_name ORDER BY SUM(sales) DESC;
SELECT DISTINCT sale_date
FROM cookie_salesORDER BY sale_date DESCLIMIT 1;
SELECT first_name, SUM(sales)
FROM cookie_salesGROUP BY first_nameORDER BY SUM(sales) DESCLIMIT 1,1;LIMIT:从0开始计数 同select搭配的有:DISTINCT,SUM.AVG,MAX,MIN,COUNT,GROUP BY, ORDER BY,LIMIT
外键:
CREATE TABLE interests
( int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, interest VARCHAR(50) NOT NULL, contact_id INT NOT NULL,CONSTRAINT my_contacts_contact_id_fk FOREIGN KEY(contact_id) REFERENCES my_contacts(contact_id));所谓的 Schema 指数据库设计模式(一对一,多对一,多对多),表之间的关系,列之间的关系等。 一对一:很少用; 一对多和多对多常用 组合主键:
CREATE TABLE super_heroes ( name VARCHAR(20) NOT NULL, power VARCHAR(25) NOT NULL, weakness VARCHAR(20) ); INSERT INTO super_heroes
VALUES ('Super Trashman','Cleans quickly','bleach'), ('Super Guy','Flies','birds'), ('Wonder Waiter','Never forgets an order','insects'); INSERT INTO super_heroes (name, power) VALUES ('The Broker','Makesmoneyfromnothing');
函数依赖:T.x -> T.y 表T的y列依赖于x列的改变 传递函数依赖:某个非键列与另一个非键列有关联 Exercise:
ALTER TABLE my_contacts ADD COLUMN interest1 VARCHAR(50), ADD COLUMN interest2 VARCHAR(50), ADD COLUMN interest3 VARCHAR(50);
UPDATE my_contactsSET interest1 = TRIM(SUBSTRING_INDEX(interests,',',1)); UPDATE my_contacts SET interest2 = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(interests,',',2),',',-1)); UPDATE my_contacts SET interest3 = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(interests,',',3),',',-1)); -- ineterests to interest4 UPDATE my_contacts SET interests = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(interests,',',4),',',-1)); ALTER TABLE my_contacts CHANGE COLUMN interests interest4 VARCHAR(100);
-- 1. create / insert / selectCREATE TABLE profession ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, profession VARCHAR(50) ); INSERT INTO profession (profession) SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession; DROP TABLE profession; -- 2. create / select CREATE TABLE profession AS SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession; ALTER TABLE profession ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id); -- 3. create / select CREATE TABLE profession ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, profession VARCHAR(50) ) AS SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession;
SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession; SELECT profession AS mc_prof FROM my_contacts GROUP BY mc_prof ORDER BY mc_prof; 内联接(Inner Join):使用条件的联接
自然连接(Natural Join)是内联接的一种,连接列名相同的。 相等联接/不等联接:内联接的两种。
交叉联接(Cross Join):也就是笛卡尔积联接 1. 交叉联接,关联子查询的速度慢; 2. 联接比子查询更有效率。
所谓的非关联子查询:指子查询能够单独运行
所谓的关联子查询:指子查询依赖外层查询 外联接: 1. 左外联接(Left Outer Join) 2. 右外联接(Right Ourter Join) 自联接 联合(Union)规则: 1. 查询的列数,类型一致 2. 查询顺序不影响结果 3. Union默认除去重复值,若保留重复值则使用Union All 检查约束,Check 1. 关键字CHECK设置列的约束条件 2. 语法: CHECK(类似where条件) 3. MySQL忽略CHECK约束,一般会用trigger代替 4.
-- ALTER TABLE my_contacts-- ADD CONSTRAINT CHECK(gender in ('M','F')); 视图(View): 1. 创建视图:
CREATE VIEW my_view AS SELECT first_name, last_name FROM my_contacts; 2. 视图实际上就是一种虚拟表,也可以理解为是后续select的逻辑名称。 3. 使用View时,可以类似表一样使用 4. View不会在内存中一直存在,可用来简化查询。可以用drop view删除视图。 5. 对MySQL来说,可以利用View加上Check Option。 事务管理:
CREATE TABLE `piggy_bank` (
`id` int(11) NOT NULL AUTO_INCREMENT, `coin` char(1) NOT NULL, `coin_year` char(4) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
START TRANSACTION;
SELECT * FROM piggy_bank;UPDATE piggy_bank SET coin = 'Q'WHERE coin='P';SELECT * FROM piggy_bank;ROLLBACK;SELECT * FROM piggy_bank;
START TRANSACTION;
SELECT * FROM piggy_bank;UPDATE piggy_bank SET coin = 'Q'WHERE coin='P';SELECT * FROM piggy_bank;COMMIT;SELECT * FROM piggy_bank;权限管理: 1. 创建用户
CREATE USER 'chris'@'localhost' IDENTIFIED BY 'chen'; 2. 利用Grant授予权限
GRANT SELECT ON my_contacts TO chris WITH GRANT OPTION; 3. 利用Revoke撤销权限
REVOKE SELECT ON my_contacts FROM chris; 有连带作用
REVOKE SELECT ON my_contacts FROM chris CASCADE; 有连带作用
REVOKE SELECT ON my_contacts FROM chris RESTRICT; 禁止连带作用 4. 角色:
CREATE ROLE data_entry;
GRANT SELECT ON my_contacts TO data_entry;-----GRANT data_entry TO chris;REVOKE FROM/GRANT TO