MySQL基本操作SQL语句

一、            数据库操作

创建数据库

CREATE DATABASE test;

查看和选择数据库

查看：SHOW DATABASES; 选择：USE test;

删除数据库

DROP DATABASE test;

 

二、            表操作

创建表

CREATE TABLEaddress(

         ID INT AUTO_INCREMENT PRIMARY KEY,

         AddressName VARCHAR(255) NOT NULL

);

CREATE UNIQUE INDEX ui_address ON address(AddressName);

CREATE TABLEperson(

         ID INT AUTO_INCREMENT PRIMARY KEY,

         LastName VARCHAR(255) NOT NULL,

         FirstName VARCHAR(255),

         Address VARCHAR(255),

         Age INT DEFAULT 0,

         Sex CHAR(2),

         CONSTRAINT ui_nameUNIQUE(LastName,FirstName),

         CONSTRAINT fk_address FOREIGNKEY(Address) REFERENCES address(AddressName)

);

先创建表address上AddressName的索引，才能创建person表上Address的外键约束。

查看表结构

USE test;

DESCRIBE person;

删除表

DROP TABLE person;

修改表

①  修改表名：ALTER TABLE person RENAME person_test;

②  修改字段数据类型：ALTER TABLE person MODIFY sex CHAR(4);

③  修改字段名字：ALTER TABLE person_test CHANGE sex Sex VARCHAR(4);

④  增加字段：ALTER TABLE person_test ADD test INT;

⑤  删除字段：ALTER TABLE person_test DROP test;

三、            触发器操作

CREATE TRIGGERbefore_trig BEFORE INSERT ON person FOR EACH ROW

INSERT INTO adress(AddressName) VALUES(new.Address);

数据库自动生成的语句

DELIMITER$$

USE`test`$$

DROPTRIGGER `before_trig`$$

CREATE

    TRIGGER `before_trig` BEFORE INSERT ON`person`

    FOR EACH ROW INSERT INTOaddress(AddressName) VALUES(new.Address);

$$

DELIMITER ;

其中，DELIMITER语句将sql语句分隔符修改为"$$"，创建完成修改为”;”。

 

四、            数据操作

插入一条：INSERT INTO person VALUES(NULL, 'Li', 'ChuanWen', 'YN',34,'male');

插入多条：INSERT INTO person VALUES       (NULL,'Huang', 'ChunWen', 'ZJ',34,'male'),

(NULL, 'L', 'Zeng', 'GX',34,'male'),

(NULL, 'Lii', 'XiaoHua', 'SZ',34,'male');

更新：UPDATEperson SET LastName = 'li' WHERE LastName = 'L' OR LastName = 'lii';

删除：DELETE FROM person WHERE LastName = 'Li';

 

五、            单表查询

避免重复查询：SELECT DISTINCT Age FROM person;

范围查询：SELECT * FROM person WHERE Age BETWEEN 10 AND 30 ;

NULL值查询：SELECT * FROM person WHERE Sex IS NULL;

IN查询：SELECT * FROM person WHERE Age IN(12,20,40,33,24);

%模糊查询： SELECT * FROM person WHERELastName LIKE 'L%';

                            SELECT* FROM person WHERE LastName LIKE '_i%';

分组查询：SELECT AVG(Age) FROM person GROUP BY Sex HAVING AVG(Age) > 24;

六、            多表查询

创建一个表

CREATE TABLEemployee(

         ID INT PRIMARY KEY AUTO_INCREMENT,

         EName VARCHAR(255),

         Department VARCHAR(255)    

);

左外连接（右表没有匹配的行用NULL代替）：

SELECT * FROM person LEFT JOIN employee ON person.LastName= employee.EName;

右外连接（左表没有匹配的行用NULL代替）：

SELECT * FROM person RIGHT JOIN employee ON person.LastName= employee.EName;