MySQL数据库基础篇-SQL

通过DDL语句对数据库进行查询、创建、删除、使用这四种操作。

#DDL-数据库操作
#查询所有数据库
SHOW DATABASES;
#创建名为itcast数据库
CREATE DATABASE itcast;
#如果数据库已存在就不要再创建
CREATE DATABASE IF NOT EXISTS itcast;
#创建test数据库
CREATE DATABASE IF NOT EXISTS test;
#创建字符集utf-8 4字节的itheima数据库
CREATE DATABASE itheima DEFAULT CHARSET utf8mb4;
#删除test数据库
DROP DATABASE test;
DROP DATABASE IF EXISTS test;
#使用itcast数据库
USE itcast;
#查询当前数据库
SELECT DATABASE();

通过DDL语句对表进行查询、创建、删除、使用这四种操作。

#查询当前数据库所有表
SHOW TABLES;
#在itcast数据库创建tb_user表
USE itcast;
CREATE TABLE tb_user(
    -> id INT COMMENT '编号',
    -> name VARCHAR(50) COMMENT '姓名',
    -> age INT COMMENT '年龄',
    -> gender VARCHAR(1) COMMENT '性别'
    -> ) COMMENT '用户表';
#查询表结构
DESC tb_user;
#查询表的建表语句
SHOW CREATE TABLE tb_user;
#创建一个员工表
 CREATE TABLE emp(
    -> id INT COMMENT '编号',
    -> workno VARCHAR(10) COMMENT '工号',
    -> name VARCHAR(10) COMMENT '姓名',
    -> gender CHAR(1) COMMENT '性别',
    -> age TINYINT UNSIGNED COMMENT '年龄',
    -> idcard CHAR(18) COMMENT '身份证号',
    -> entrydate DATE COMMENT '入职时间'
    -> ) COMMENT '员工表';
#为emp表增加一个nickname字段
ALTER TABLE emp ADD nickname VARCHAR(20) COMMENT '昵称';
#查询表结构
DESC emp;

#修改emp表的nickname字段为username字段
ALTER TABLE emp CHANGE nickname username VARCHAR(30) COMMENT '用户名';
#查询表结构
DESC emp;

#删除emp表的username字段
ALTER TABLE emp DROP username;
#查询表结构
DESC emp;

#修改emp表名为employee
ALTER TABLE emp RENAME TO employee;

#删除tb_user表
DROP TABLE IF EXISTS tb_user;
#删除指定表并重新创建该表
TRUNCATE TABLE employee;

通过DML语句对表进行增、删、改这三种操作。

#增加
INSERT INTO employee(id, workno, name, gender, age, idcard, entrydate) VALUES (1, '1', 'Itcast', '男', 10, '123456789012345678', '2000-01-01');
INSERT INTO employee VALUES (2, '2', '张无忌', '男', 18, '123456789012345670', '2005-01-01');
INSERT INTO employee VALUES (3, '3', '韦一笑', '男', 38, '123456789712345670', '2005-01-01'), (4, '4', '赵敏', '女', 18, '12345675712345670', '2005-01-01');

#修改
UPDATE employee SET name = 'itheima' where id = 1;
UPDATE employee SET name = '小昭', gender = '女' where id = 1;
UPDATE employee SET entrydate = '2008-01-01';

#删除
DELETE FROM employee WHERE gender = '女';
DELETE FROM employee;

SELECT * FROM employee;

通过DQL语句进行查询。

select database();
create table emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    workaddress varchar(50) comment '工作地址',
    entrydate date comment '入职时间'
) comment '用户表';

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)VALUES (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),
    (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
    (3, '3', '韦一笑', '男', 38, '123456789012345670', '上海', '2005-08-01'),
    (4, '4', '赵敏', '女', 18, '123456789012345670', '北京', '2009-12-01'),
    (5, '5', '小昭', '女', 16, '123456789012345678', '上海', '2007-07-01'),
    (6, '6', '杨逍', '男', 28, '12345678901234567X', '北京', '2006-01-01'),
    (7, '7', '范瑶', '男', 40, '123456789012345670', '北京', '2005-05-01'),
    (8, '8', '黛绮丝', '女', 38, '123456789012345670', '天津', '2015-05-01'),
    (9, '9', '范凉凉', '女', 45, '123456789012345678', '北京', '2010-04-01'),
    (10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
    (11, '11', '张士诚', '男', 55, '123456789012345670', '江苏', '2015-05-01'),
    (12, '12', '常遇春', '男', 32, '123456789012345670', '北京', '2004-02-01'),
    (13, '13', '张三丰', '男', 88, '123456789012345678', '江苏', '2020-11-01'),
    (14, '14', '灭绝', '女', 65, '123456789012345670', '西安', '2019-05-01'),
    (15, '15', '胡青牛', '男', 70, '12345678901234567X', '西安', '2018-04-01'),
    (16, '16', '周芷若', '女', 18, NULL, '北京', '2012-06-01');

select name, workno, age from emp;
select id,workno,name,gender,age,idcard,workaddress,entrydate from emp;
select * from emp;
select workaddress as '工作地址' from emp;
select workaddress '工作地址' from emp;
select distinct workaddress '工作地址' from emp;

select * from emp where age = 88;
select * from emp where age < 20;
select * from emp where age <= 20;
select * from emp where idcard is null;
select * from emp where idcard is not null;
select * from emp where age != 88;
select * from emp where age <> 88;
select * from emp where age>=15 and age<=20;
select * from emp where age>=15 && age<=20;
select * from emp where age between 15 and 20;
select * from emp where gender='女' and age<25;
select * from emp where age=18 or age=20 or age=40;
select * from emp where age in (18,20,40);
select * from emp where name like '__';
select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';

select count(*) from emp;
select count(name) from emp;
select count(idcard) from emp;
select avg(age) from emp;
select max(age) from emp;
select min(age) from emp;
select sum(age) from emp where workaddress = '西安';

select gender, count(*) from emp group by gender;
select gender, avg(age) from emp group by gender;
select workaddress, count(*) address_count from emp where age<45 group by workaddress having address_count>=3;

select * from emp order by age;
select * from emp order by age desc ;
select * from emp order by entrydate desc ;
select * from emp order by age, entrydate desc ;

select * from emp limit 0, 10;
select * from emp limit 10;
select * from emp limit 10, 10;

select * from emp where gender='女' and age in (20,21,22,23);
select * from emp where gender='男' and (age between 20 and 40) and name like '___';
select gender, count(*) from emp where age<60 group by gender;
select name, age from emp where age<=35 order by age, entrydate desc ;
select * from emp where gender='男' and age between 20 and 40 order by age, entrydate limit 0, 5;

select name,age from emp where age>15 order by age;

create user 'itcast'@'localhost' identified by '123456';
create user 'itheima'@'%' identified by '123456';
alter user 'itheima'@'%' identified with mysql_native_password by '1234';
drop user 'itcast'@'localhost';
drop user 'itheima'@'%';
create user 'itheima'@'%' identified by '1234';
drop user 'itheima'@'%';
create user 'heima'@'%' identified by '1234';

show grants for 'heima'@'%';
grant all on itcast.* to 'heima'@'%';
revoke all on itcast.* from 'heima'@'%';