常用语句:
show databases(显示现存数据库,注意是复数)
use design(使用数据库)
show tables(查看这个数据库下面的表)
desc studios(显示表的结构)
创建表:
CREATE TABLE Studios(name CHAR(20), city VARCHAR(50), state CHAR(2), revenue FLOAT)
指定主键和候选键:UNIQUE也是用来规范属性唯一
CREATE TABLE Studios (studio_id INTEGER, name CHAR(20), city VARCHAR(50), state CHAR(2), PRIMARY KEY(studio_id), UNIQUE(name))
或:
CREATE TABLE Studios3 (studio_id INTEGER PRIMARY KEY, name CHAR(20) UNIQUE, city VARCHAR(50), state CHAR(2))
CREATE TABLE test1 (id INT IDENTITY(1, 1) PRIMARY KEY, name VARCHAR(MAX), url VARCHAR(MAX));
建立外键:
CREATE TABLE Movies(movie_title VARCHAR(40),studio_id INTEGER REFERENCES Studios(studio_id)) 加入外键后要原来的Studios表中也有相应的studio_id才能插入
定义非空值:
CREATE TABLE Studios(studio_id INTEGER PRIMARY KEY, name CHAR(20) NOT NULL, city VARCHAR(50) NOT NULL, state CHAR(2) NOT NULL)
CHECK添加约束:
CREATE TABLE Movies(movie_titles VARCHAR(40) PRIMARY KEY, studio_id INTEGER, budget FLOAT CHECK(budget > 500000))
给约束命名:
CREATE TABLE Movies(movie_titles VARCHAR(40) PRIMARY KEY, studio_id INTEGER, budget FLOAT, constraint budget_constraint CHECK(budget > 500000))
设置默认值:
CREATE TABLE Movies(movie_title VARCHAR(40) NOT NULL, release_date DATE DEFAULT SYSDATE NULL, genre VARCHAR(20) DEFAULT ‘Comedy’ CHECK(genre IN(‘Horror’, ‘Comedy’, ‘Drama’)))
修改变结构添加列:
ALTER TABLE movies ADD author VARCHAR(20) not null
删除列:
ALTER TABLE movies DROP COLUMN author
修改列:
ALTER TABLE movies MODIFY author char(3) not null
INSERT语句(将属性补充完成):
INSERT INTO Studios(city, state, studio_id) VALUES(‘Burbank’,‘MPM’,2)
UPDATE语句:
UPDATE Studios SET city =‘New York’, state = ‘NY’WHERE studio_id = 1
DELETE语句:
DELETE FROM Studios WHERE state = ‘AK’
SELECT语句(最好也是将属性补充完整):
SELECT * FROM emp WHERE sal = ‘AAABAA’
SELECT empno, ename, sal, job FROM emp WHERE job = ‘SALESMAN’AND sal < 3000
字符串函数大小写转换upper,lower:
SELECT * FROM emp WHERE lower(job) = ‘salesman’ and sal < 3000
更改属性显示名称:
SELECT job AS l_job FROM emp WHERE low(job) = ‘salesman’ and sal < 3000
SELECT ename,sal AS ‘工资’,sal*1.5 AS ‘加班工资’FROM emp
运算符先后顺序(高到底顺序排列):
运算符 |
用法 |
() |
括号 |
/ , * , - , +(左边大于右边) |
算术运算符 |
AND, OR, NOT |
布尔运算符 |
比较运算:
大于:> 小于:< 等于:= 不等于:<>
IN语句: