如何查看所有的数据库:
Show databases;
如何进入某个数据库:
use xxx;
如何新进数据库:
Create database jx;
如何删除数据库:
Drop database jx;
如何查看所有的表格:
Show tables;
如何创建数据表:
create table teacher(id int,name
varchar(10),address varchar(100),score float,time date);
如何修改表(添加列):
alter table teacher add phone varchar(11);
如何修改表(删除列):
alter table teacher drop score;
如何修改表(修改列):
alter table teacher modify phone int;
如何删除表:
drop table student;
表的约束管理:
非空约束 not null
唯一约束 unique
主键约束 primary key
默认约束 default
示例:
create table student
(id int primary key, name varchar(10) not null, phone varchar(11) default "18502348498",address
varchar(100) unique)
对于数据库中表的操作有4种操作:
增删改查
增加操作:
INSERT INTO employees_cn
(employee_name, employee_address, employee_price) VALUES ("李兰","长沙",14500),("李兰妈妈","株洲",9000);
删除操作:
DELETE FROM employees_cn WHERE employee_name="诸葛亮";
DELETE FROM employees_cn WHERE employee_name="周杰" AND employee_address = "深圳";
修改操作:
UPDATE employees_cn set employee_address = "佛山" , employee_price = 51000 WHERE employee_name = "马超";
查询操作:
SELECT * from employees_cn WHERE employee_price >= 20000;
SELECT 1+2*8+5/2 as result;
去重:
SELECT DISTINCT employees_price FROM employees_cn;
分页:
SELECT * FROM city LIMIT 3,15;
解释:3:是从4开始,不包括3
15:往后数15行。
使用完全限定表名:
SELECT city.population FROM city;