mysql命令大全实用

（1）在example数据库下创建college表。SQL代码如下： CREATE TABLE college ( number INT(10) NOT NULL UNIQUE PRIMARY KEY , name VARCHAR(20) NOT NULL , major VARCHAR(20) NOT NULL , age INT(5) );

（2）使用CREATE VIEW语句来创建视图college_view。SQL代码如下： CREATE ALGORITHM=UNDEFINED VIEW

college_view (student_num, student_name, student_age, department ) AS SELECT number, name, age, major FROM college WITH LOCAL CHECK OPTION;

（3）执行SHOW CREATE VIEW语句来查看视图的详细结构。代码如下： SHOW CREATE VIEW college_view \\G

（4）更新视图。向视图中插入三条记录，SQL代码如下：

INSERT INTO college_view VALUES( 0901, '张三', 20, '外语' ); INSERT INTO college_view VALUES( 0902, '李四', 22, '计算机' ); INSERT INTO college_view VALUES( 0903, '王五', 19, '计算机' );

（5）修改视图，使视图中只显示专业为“计算机”的信息。SQL代码如下： CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW college_view (student_num, student_name, student_age, department ) AS SELECT number, name, age, major FROM college WHERE major='计算机' WITH LOCAL CHECK OPTION;

（6）删除视图。SQL代码如下： DROP VIEW college_view; 九、

1．创建product表和operate表 创建product表的SQL代码如下： CREATE TABLE product ( id INT(10) NOT NULL UNIQUE PRIMARY KEY , name VARCHAR(20) NOT NULL , function VARCHAR(50) , company VARCHAR(20) NOT NULL, address VARCHAR(50) );

创建operate表的SQL代码如下： CREATE TABLE operate ( op_id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT , op_name VARCHAR(20) NOT NULL , op_tiem TIME NOT NULL );

2．创建product_bf_insert触发器

创建product_bf_insert的SQL代码如下：

CREATE TRIGGER product_bf_insert BEFORE INSERT ON product FOR EACH ROW INSERT INTO operate VALUES(null, 'Insert product', now());

3．创建product_af_update触发器

创建product_af_update的SQL代码如下：

CREATE TRIGGER product_af_update AFTER UPDATE ON product FOR EACH ROW INSERT INTO operate VALUES(null, 'Update product', now());

4．创建product_af_del触发器

创建product_af_del的SQL代码如下：

CREATE TRIGGER product_af_del AFTER DELETE ON product FOR EACH ROW INSERT INTO operate VALUES(null, 'delete product', now());

5．对product表进行操作

向product表中插入一条记录。SQL代码执行如下：

INSERT INTO product VALUES(1, 'abc','治疗感冒', '北京abc制药厂','北京市昌平区'); SELECT * FROM operate;

更新记录，SQL代码如下：

UPDATE product SET address='北京市海淀区' WHERE id=1; SELECT * FROM operate;

删除记录，SQL代码如下：

DELETE FROM product WHERE id=1; SELECT * FROM operate;

6．删除触发器

删除触发器product_bf_insert，SQL代码执行如下： DROP TRIGGER product_bf_insert;

删除触发器product_af_update，SQL代码执行如下： DROP TRIGGER product_af_update;

十、

（1）创建触发器，SQL代码如下： 创建product_af_insert触发器

CREATE TRIGGER product_af_insert AFTER INSERT ON product FOR EACH ROW INSERT INTO operate VALUES(null, 'Insert product', now());

创建product_bf_update触发器

CREATE TRIGGER product_bf_update BEFORE UPDATE ON product FOR EACH ROW INSERT INTO operate VALUES(null, 'Update product', now());

创建product_bf_update触发器

CREATE TRIGGER product_bf_del BEFORE DELETE ON product FOR EACH ROW INSERT INTO operate VALUES(null, 'delete product', now());

（2）查看product_bf_del触发器的基本结构，代码如下：

SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='product_bf_del'\\G

（3）插入、更新和删除product表中的信息，SQL代码如下：

INSERT INTO product VALUES(2, 'ccc','止血', '北京ccc制药厂','北京市昌平区'); UPDATE product SET address='天津市开发区' WHERE id=2; DELETE FROM product WHERE id=2;

（4）删除触发器product_bf_update和product_bf_del，代码如下： DROP TRIGGER product_bf_update; DROP TRIGGER product_bf_del;

十一、

CREATE TABLE grade( num INT(10) NOT NULL , course VARCHAR(10) NOT NULL , score FLOAT );

INSERT INTO grade VALUES(1001,'数学',80); INSERT INTO grade VALUES(1001,'语文',90); INSERT INTO grade VALUES(1001,'英语',85); INSERT INTO grade VALUES(1001,'计算机',95);

INSERT INTO grade VALUES(1002,'数学',88); INSERT INTO grade VALUES(1002,'语文',90);

INSERT INTO grade VALUES(1002,'英语',89); INSERT INTO grade VALUES(1002,'计算机',90);

INSERT INTO grade VALUES(1003,'数学',80); INSERT INTO grade VALUES(1003,'语文',98); INSERT INTO grade VALUES(1003,'英语',85); INSERT INTO grade VALUES(1003,'计算机',95);

CREATE TABLE scholarship( level INT PRIMARY KEY, score INT );

INSERT INTO scholarship VALUES(1,90); INSERT INTO scholarship VALUES(2,80); INSERT INTO scholarship VALUES(3,70);

CREATE TABLE employee( num INT PRIMARY KEY NOT NULL AUTO_INCREMENT, d_id INT NOT NULL, name VARCHAR(20) , age INT, sex VARCHAR(4), homeaddr VARCHAR(50) );

INSERT INTO employee VALUES(NULL,1001,'张三',26,'男','北京市海淀区'); INSERT INTO employee VALUES(NULL,1001,'李四',24,'女','北京市昌平区'); INSERT INTO employee VALUES(NULL,1002,'王五',25,'男','湖南长沙市'); INSERT INTO employee VALUES(NULL,1004,'Aric',15,'男','England');

CREATE TABLE computer_stu( id INT PRIMARY KEY, name VARCHAR(20), score FLOAT );

INSERT INTO computer_stu VALUES(1001,'Lily',85); INSERT INTO computer_stu VALUES(1002,'Tom',91); INSERT INTO computer_stu VALUES(1003,'Jim',87); INSERT INTO computer_stu VALUES(1004,'Aric',77);