简单的SQL语句

-- 注释
DROP TABLE stu; -- 删除表

CREATE TABLE stu (
    id INT ( 10 ) PRIMARY KEY auto_increment,
    sname VARCHAR ( 50 ) NOT NULL,
    age INT ( 3 ) DEFAULT 18,
    gender VARCHAR ( 1 ) DEFAULT '男',
    phone VARCHAR ( 11 ) UNIQUE,
    birthday datetime 
);-- 创建表

INSERT INTO stu ( sname, age, gender, phone, birthday )
VALUES
    ( 'lily', 18, '男', '13260192328', '1989-01-30 12:09:17' ); -- 插入数据
    
DELETE FROM stu WHERE sname = 'lily'; -- 删除数据
    
INSERT INTO stu ( sname, age, gender, phone, birthday )
VALUES
    ( 'lily2', 18, '男', '13260192221', '1989-01-30 12:09:17' ); -- 插入数据
    
UPDATE stu SET phone = '13051943121' WHERE     sname = 'lily2'; -- 修改

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
    `class` varchar(10),
    `score` int(10),
  PRIMARY KEY (`id`)
) ;

-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1', 'user1', '18', '1881231231', '北京市昌平区回龙观新龙城3区1栋203室', '一班', 190);
INSERT INTO `person` VALUES ('2', 'user2', '19', '18614075987', '北京市昌平区沙河镇汇德商厦走廊', '一班',80);
INSERT INTO `person` VALUES ('3', 'user3', '23', '18614075999', '北京市八大胡同皮条胡同左边第一家', '二班', 998);
INSERT INTO `person` VALUES ('4', 'user4', '35', '19912345678', null, '一班',861);
INSERT INTO `person` VALUES ('5', 'user4', '40', '19945612345', '新家', '二班', 150);
INSERT INTO `person` VALUES ('6', 'user5', '12', '16632512365', '幸福家', '二班', 590);

-- 查询年龄大于20 或者名字是user2的
select * from person where age > 20 or name='user2'; 

-- 查询年龄在20和30之间的
select * from person where age between 20 and 30; 

-- 查询手机号码在（）内的
select * from person where phone in ('1881231231', '19912345678','16632512365');

-- 查询地址为空的
select * from person where address is null; 

-- 查询地址不为空的
select * from person where address is not null; 

-- 分组查询group by
-- 聚合函数 count()总数 max()最大值 min()最小值 sum()求和 avg()平均值

-- 查询班级的人数 最高分 最低分 平均分 总分
select count(*), max(score), min(score), avg(score), sum(score) from person group by class; 

-- 查询学生分数大于班级平均分数的学生信息
select * from person p1 where score > (select avg(score) from person p2 where p1.class=p2.class group by class);

-- 查询平均分数大于500的班级
select class, avg(score) from person group by class having avg(score) > 500;

-- having 和where 筛选
-- where 对原始数据进行筛选
-- having 对聚合函数后的数据进行筛选

-- order by 排序
-- asc 从小到大 默认
-- desc 从大到小 
-- 根据年龄对学生进行排序
select name, age from person order by age;

-- 分页 limit start, size
insert into person(name, age, address, class, score, phone) values (111, 1111, 2222, '三班',120, 10086 );



-- 每页显示10条数据
-- 第一页 0, 10  (page-1)*pageSize, pageSize  
-- 第二页 10,10  
-- 第三页 20，10

-- 查询第3页的数据
select * from person limit 20, 10;


-- 创建表
create table dept(
    id int(10) primary key auto_increment,
    dept_name varchar(100),
    dept_leader varchar(100)
);

-- 创建表
create table emp_new(
    id int(10) primary key auto_increment,
    name varchar(100),
    age int(5),
    dept_id int(10),
    constraint fk_detp_emp_id foreign key(dept_id) REFERENCES dept(id)
);

-- 插入部门表数据
insert into dept(dept_name, dept_leader) values 
('销售', '张大'),
('人事', '张三'),
('技术', '李四'),
('开发', '王五')
;

-- 插入员工数
insert into emp_new(name, age, dept_id) values
('user1', 18, 1),
('user2', 18, 1),
('user3', 18, 2),
('user4', 18, 2),
('user5', 18, 3),
('user6', 18, 3),
('user7', 18, 4),
('user8', 18, 4),


-- 多表（外键）
-- inner join on 左右两边的表满足on条件
select * from emp_new inner join dept on emp_new.dept_id=dept.id; 
-- left join on 左连接,把左边表的数据都显示出来，把右边表能和左边表连接上的显示出来
select * from emp_new left join dept on emp_new.dept_id=dept.id; 

-- 查询出每个帮派的平均年龄
select dept.dept_name, avg(age) from emp_new inner join dept on emp_new.dept_id=dept.id group by dept.id;

SQL : Structured Query Language 结构化查询语言

---------------------------------------------------数据库的增删改查---------------------------------------------------

mysql> create database db1 charset gbk; # 创建库db1,编码gbk
Query OK, 1 row affected (0.00 sec)

mysql> show databases; # 查库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> show create database db1; # 查库db1的创建信息
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database db1 charset utf8; # 修改库db1的编码为utf8
Query OK, 1 row affected (0.00 sec)

mysql> show create database db1; # 查库db1的创建信息
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop database db1; # 删除库db1
Query OK, 0 rows affected (0.00 sec)

mysql>

---------------------------------------------------数据表的增删改查---------------------------------------------------

---------------------------------------------------表中数据的操作---------------------------------------------------

mysql> create table t1(id int auto_increment primary key, name char(10)); # auto_increment自增, primary key 主键
Query OK, 0 rows affected (0.23 sec)

mysql> insert into t1 values(123, "张三"), (1234567891, "李四"), (12345678910, "一二三四五六七八九十十一"); # 有没有发现int超过字符数,就变为2147483647,char超过自动截取
Query OK, 3 rows affected, 2 warnings (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 2

mysql> select * from t1;
+------------+--------------------------------+
| id         | name                           |
+------------+--------------------------------+
|        123 | 张三                           |
| 1234567891 | 李四                           |
| 2147483647 | 一二三四五六七八九十           |
+------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> desc t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| name | char(10) | YES |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> delete from t1; # 有自增id,新增的数据,仍然是以删除前的最后一条作为起始
Query OK, 3 rows affected (0.02 sec)

mysql> insert into t1(name) values("王五");
Query OK, 1 row affected (0.05 sec)

mysql> select * from t1;
+------------+--------+
| id         | name   |
+------------+--------+
| 2147483647 | 王五   |
+------------+--------+
1 row in set (0.01 sec)

mysql> truncate table t1; # 数据量大,删除速度比上一条快,切直接从零开始
Query OK, 0 rows affected (0.19 sec)

mysql> insert into t1(name) values("王五");
Query OK, 1 row affected (0.03 sec)

mysql> select * from t1;
+----+--------+
| id | name   |
+----+--------+
| 1 | 王五   |
+----+--------+
1 row in set (0.00 sec)

mysql>

posted on 2019-06-18 17:54 lilyxiaoyy 阅读(238) 评论(0) 编辑收藏举报