SQL语句:
1:创建数据库:CREATE DATABASE example
2: 创建表: CREATE TABLE example(id INT ,name VARCHAR(20),SEX BOOLEAN);
设置主键 CREATE TABLE example(id INT PRIMARY KEY ,name VARCHAR(20),SEX BOOLEAN);
设置约束 NOTNULL UNIQUE(唯一值) AUTO INCREASEMENT 自增 DEFAULT 20 默认为20
3:修改:
修改表名:ALERTE TABLE example RENAME user;
修改字段属性:ALERTE TABLE example MODIFY 属性名 类型;
ALERTE TABLE example CHANGE 属性名 新属性名 新类型;
在表中添加新字段: ALERTE TABLE example ADD adress VARCHAR(30) NOT NULL AFTER phone;
删除字段: ALERTE TABLE example DROP phone;
更改引擎 ALERTE TABLE 表名 ENGINE 存储引擎
视图:对外提供的一个窗口,可以选择性的让用户看到想要的字段
创建视图:CREATE VIEW depatement_view(name,function,location) AS SELECT d_name,funcion,adrres form departement;
查看视图:DESCRIBLE s视图名
4:查找语句
SLECT num,name,id,sex FROM employee WHERE age<26 ORDER BY d_id DESC;
SLECT *FROM employee;
SLECT *FROM employee WHERE d_id=1001;
在集合中条件:SLECT *FROM employee WHERE d_id IN(1001,1004);
age BETWEEN 15 AND 25;
正则表达式查询:SLECT *FROM employee info WHERE name REGXEXP "";
查询匹配条件:SLECT *FROM employee WHERE address LIKE "aric";
多条件查询:SLECT *FROM employee WHERE d_id=1001 AND address LIKE "aric";
LIMIT 限制查询的数量:SLECT *FROM employee LIMIT 2;//查询两条
LIMIT 0,2; //从1 开始查询两条
ANY 关键字:SLECT *FROM employee WHERE score >=ANY (SLECT score FROM scholrship) //查询满足条件 大于最小值
ALL 关键字:SLECT *FROM employee WHERE score >=ALL (SLECT score FROM scholrship) //employee中的score必须大于scholrship中score的最大值
5 插入更新
INSERT INTO 表名 VALUES(值1,值2,值3,...)
INSERT INTO PRODUCTE(id,name,function) VALUES(1001,"bxs","ddd") ,(1002,"ddfsf","dsfd")//同时插入多条记录
将查询结果插入到表中”INSERT INTO PRODUCTE(id,name,function) SLECT id,name,function FROM medcion;
更新数据:UPDATA product SET name="aaa感冒药",address="北京" ,WHERE id=1;
删除数据:DELECT FROM product WHERE id=1001;
DELECT FROM product//全部删除