Skip to content

Mysql使用

操作环境

Ubuntu 24.04

Mysql 数据库安装

WSL中安装

sudo apt install mysql-server

以root用户登录并修改root用户密码

执行以下语句，强制以root用户登录 

sudo mysql -u root
修改密码: 
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

基本操作

创建数据库

CREATE DATABASE db_name;

查询当前所有数据库的名称

SHOW DATABASES;

创建数据表

CREATE TABLE table_name (name TYPE CONSTRAINTS,...);
创建样例
create table instructor(
    ID char(5),
    name varchar(20) not null,
    dept_name varchar(20),
    salary numeric(8,2),
    primary key(ID),
    foreign key(dept_name) references department
    on delete cascade | set null | restrict | set default
    on update cascade | set null | restrict | set default,
);

插入数据

INSERT INTO table_name (name, age) VALUES ('John', 30);

查询数据

查询样例
select '437' as FOO 
/* Results is a table with one column and a single row with value '437'*/
select 'A' from instructor
/*Result is a table with one column and N rows(number of tuples in the instructors table), each row with value 'A'*/
select ID,name, salary/12 from instructor
/* would return a relation that is the same as the instructor relation, except thaht the value of the attribute salary is divided by 12.*/
select distinct dept_name from instructor
/* find the department name of all insturctors, and remove duplicates*/
select all dept_name from intructor
/* The keyword all specifies that duplicates should not be removed */

SELECT * FROM table_name WHERE ... AND ...;

删除字段

ALTER TABLE table_name DROP COLUMN column_name;

建立索引

CREATE INDEX index_name ON table_name (column_name);

删除索引

DROP INDEX index_name ON table_name;

建立视图

CREATE VIEW view_name AS SELECT column_name FROM table_name WHERE ...;

删除视图

DROP VIEW view_name;

删除数据

DELETE FROM table_name WHERE ...;

进阶操作

集合运算

(select course_id from section where sem='Fall' and year = 2017) union (select course_id from section where sem='Spring' and year = 2018)
/* Find courses that ran in Fall 2017 or in Spring 2017*/

(select course_id from section where sem ='Fall' and year = 2017) intersect (select course_id from section where sem='Spring' and year =2018)
/*Find courses that ran in Fall 2017 and in Spring 2018*/

(select course_id from section where sem = 'Fall' and year = 2017) except (select course_id from section where sem='Spring' and year = 2018)
/* Find courses that ran in Fall 2017 but not in Spring 2018*/
union (all)

select distinct course_id from section where semester = 'Fall' and year = 2017 and course_id in\not in(select course_id from section where semester= 'Spring' and year = 2018);

select name from instructor  where salary > some/all (select salary from instructor where dept_name = 'Biology')

exits 判断集合是否有值
unique 判断集合的值是否唯一

字符串运算

  • percent (%). The % character matches any substring.
  • underscore (_). The _ character matches any character. 
select name from instructor where name like '%dar%'
/* Find the names of all instructors whoes name includes the substring 'dar' */
like '100 \%' escape '\'
/*Match the string '100%'*/

'Intro%' /* matches any string beginning with 'Intro' */
'___' /*matches any string of exactly three characters.*/
'___%' /* matches any string of at least three characters*/

重命名

select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = 'Comp. Sci.'

分类COUNT

SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department;

跨表查询

SELECT title FROM book t1 WHERE EXISTS ( SELECT * FROM borrow t2 WHERE t1.bno = t2.bno);

排序

SELECT * FROM table_name ORDER BY column_name ASC/DESC;

添加约束

添加主键 

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

添加外键(级联删除和级联更新)

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table_name(referenced_column_name) ON DELETE CASCADE ON UPDATE CASCADE;

添加内容约束 

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name > 0);

添加触发器

定义结束符,用于命令行的程序书写 

DELIMITER $$

定义触发器 

CREATE TRIGGER trigger_name BEFORE INSERT ON table_name 
    FOR EACH ROW 
BEGIN
    DECLARE var_name TYPE;
    IF condition THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'message';
    END IF;
END$$

恢复分号结束符 

DELIMITER ;

权限控制

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

给予权限

GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'host' on grant option;

撤销权限

REVOKE ALL PRIVILEGES ON db_name.* FROM 'username'@'host';

内置函数

  • avg\min\max\sum\count
  • group by 
    select dept_name, avg(salary) as avg_salary from instructor group by dept_name having avg(salary) > 42000;
/*predicates in the having clause are applied after the formation of groups whereas predicates in the where clause are applied before forming groups*/