mysql 学习记录(一)--建表,增加,删除,修改,基本查询

1.建立数据库
mysql> create database test1;
Query OK, 1 row affected (0.02 sec)

2.建立了数据再建立,则:
mysql> create database test1;
ERROR 1007 (HY000): Can't create database 'test1'; database exists

3.显示数据库信息:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test1              |
+--------------------+
5 rows in set (0.05 sec)

4.进入数据库
mysql> use test1;
Database changed

5.显示表中的数据库:
mysql> show tables;
Empty set (0.00 sec)

6.删除数据库:
mysql> drop database test1;
Query OK, 0 rows affected (0.07 sec)

7.建立表:
mysql> create database test1;
Query OK, 1 row affected (0.01 sec)
mysql> use test1;
Database changed
mysql> create table emp(ename varchar(10), hiredate date , sal decimal(10,2), deptno int(2));
Query OK, 0 rows affected (0.05 sec)
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

8.显示DDL语句:
mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

9.删除表:
mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)

10.修改表字段名称,删除表字段
mysql> create table emp(ename varchar(10), hiredate date , sal decimal(10,2), deptno int(2));\
Query OK, 0 rows affected (0.03 sec)

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp drop column age;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table emp change age age1 int(4);
ERROR 1054 (42S22): Unknown column 'age' in 'emp'
mysql> alter table emp change sal sal_salart int(4);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ename      | varchar(20) | YES  |     | NULL    |       |
| hiredate   | date        | YES  |     | NULL    |       |
| sal_salart | int(4)      | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

11.修改表字段顺序:
mysql> alter table emp add birth date after ename;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table emp add testColumn date after ename;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ename      | varchar(20) | YES  |     | NULL    |       |
| testColumn | date        | YES  |     | NULL    |       |
| birth      | date        | YES  |     | NULL    |       |
| hiredate   | date        | YES  |     | NULL    |       |
| sal_salart | int(4)      | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp modify sal_salart int(5) first;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| sal_salart | int(5)      | YES  |     | NULL    |       |
| ename      | varchar(20) | YES  |     | NULL    |       |
| testColumn | date        | YES  |     | NULL    |       |
| birth      | date        | YES  |     | NULL    |       |
| hiredate   | date        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp rename emp_test;
Query OK, 0 rows affected (0.01 sec)

12.增加字段:
mysql> alter table emp_test add column age int(3);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp_test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| sal_salart | int(5)      | YES  |     | NULL    |       |
| ename      | varchar(20) | YES  |     | NULL    |       |
| testColumn | date        | YES  |     | NULL    |       |
| birth      | date        | YES  |     | NULL    |       |
| hiredate   | date        | YES  |     | NULL    |       |
| age        | int(3)      | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table emp_test rename test;
Query OK, 0 rows affected (0.01 sec)

13.插入数据:
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> insert into test(ename,age) values('abc',3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
+------------+-------+------------+-------+----------+------+
1 row in set (0.00 sec)

mysql> insert into test(ename,age) values('abc',3),('def',9);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
|       NULL | def   | NULL       | NULL  | NULL     |    9 |
+------------+-------+------------+-------+----------+------+
3 rows in set (0.00 sec)

14.修改数据:
mysql> update test set age = 8 where ename = 'def';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
|       NULL | def   | NULL       | NULL  | NULL     |    8 |
+------------+-------+------------+-------+----------+------+
3 rows in set (0.00 sec)

mysql> update test set age = 8 where ename = 'def22222222';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from test;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
|       NULL | def   | NULL       | NULL  | NULL     |    8 |
+------------+-------+------------+-------+----------+------+
3 rows in set (0.00 sec)

mysql> update test set age = 81 where ename = 'def22222222';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from test;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
|       NULL | def   | NULL       | NULL  | NULL     |    8 |
+------------+-------+------------+-------+----------+------+
3 rows in set (0.00 sec)

mysql> update test set age = 81 where ename = 'def';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
|       NULL | abc   | NULL       | NULL  | NULL     |    3 |
|       NULL | def   | NULL       | NULL  | NULL     |   81 |
+------------+-------+------------+-------+----------+------+
3 rows in set (0.00 sec)

15.删除数据
mysql> delete from test where age = 3;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | def   | NULL       | NULL  | NULL     |   81 |
+------------+-------+------------+-------+----------+------+
1 row in set (0.00 sec)

mysql> insert test(ename,age) values ('aaaaa',3),('aaaaa',5),('ccccc',3), ('aaaaa',9), ('ttttt',2);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | def   | NULL       | NULL  | NULL     |   81 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |
|       NULL | ccccc | NULL       | NULL  | NULL     |    3 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |
|       NULL | ttttt | NULL       | NULL  | NULL     |    2 |
+------------+-------+------------+-------+----------+------+
6 rows in set (0.00 sec)

16.查询数据:
mysql> alter table test rename test_col;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test_col;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | def   | NULL       | NULL  | NULL     |   81 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |
|       NULL | ccccc | NULL       | NULL  | NULL     |    3 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |
|       NULL | ttttt | NULL       | NULL  | NULL     |    2 |
+------------+-------+------------+-------+----------+------+
6 rows in set (0.00 sec)

mysql> alter table test_col rename test;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | def   | NULL       | NULL  | NULL     |   81 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |
|       NULL | ccccc | NULL       | NULL  | NULL     |    3 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |
|       NULL | ttttt | NULL       | NULL  | NULL     |    2 |
+------------+-------+------------+-------+----------+------+
6 rows in set (0.00 sec)

mysql> selecdt distinct ename from test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selecdt distinct ename from test' at line 1
mysql> select distinct ename from test;
+-------+
| ename |
+-------+
| def   |
| aaaaa |
| ccccc |
| ttttt |
+-------+
4 rows in set (0.00 sec)

mysql> select * from test where ename = 'aaaaa';
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |
+------------+-------+------------+-------+----------+------+
3 rows in set (0.00 sec)

mysql> select * from test where age < 8;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |
|       NULL | ccccc | NULL       | NULL  | NULL     |    3 |
|       NULL | ttttt | NULL       | NULL  | NULL     |    2 |
+------------+-------+------------+-------+----------+------+
4 rows in set (0.00 sec)

mysql> select * from test order by age;
+------------+-------+------------+-------+----------+------+
| sal_salart | ename | testColumn | birth | hiredate | age  |
+------------+-------+------------+-------+----------+------+
|       NULL | ttttt | NULL       | NULL  | NULL     |    2 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |
|       NULL | ccccc | NULL       | NULL  | NULL     |    3 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |
|       NULL | def   | NULL       | NULL  | NULL     |   81 |
+------------+-------+------------+-------+----------+------+
6 rows in set (0.00 sec)

mysql> alter table test add column salart int (11);
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> alter table test change salart salary int (11);
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------------+-------+------------+-------+----------+------+--------+
| sal_salart | ename | testColumn | birth | hiredate | age  | salary |
+------------+-------+------------+-------+----------+------+--------+
|       NULL | def   | NULL       | NULL  | NULL     |   81 |   NULL |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |   NULL |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |   NULL |
|       NULL | ccccc | NULL       | NULL  | NULL     |    3 |   NULL |
|       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |   NULL |
|       NULL | ttttt | NULL       | NULL  | NULL     |    2 |   NULL |
+------------+-------+------------+-------+----------+------+--------+
6 rows in set (0.00 sec)

mysql> insert into test(ename,age,salary) values  ('newNane1',1,100),
    -> ('newName2',3,80),
    -> ('newName3',5,10);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------------+----------+------------+-------+----------+------+--------+
| sal_salart | ename    | testColumn | birth | hiredate | age  | salary |
+------------+----------+------------+-------+----------+------+--------+
|       NULL | def      | NULL       | NULL  | NULL     |   81 |   NULL |
|       NULL | aaaaa    | NULL       | NULL  | NULL     |    3 |   NULL |
|       NULL | aaaaa    | NULL       | NULL  | NULL     |    5 |   NULL |
|       NULL | ccccc    | NULL       | NULL  | NULL     |    3 |   NULL |
|       NULL | aaaaa    | NULL       | NULL  | NULL     |    9 |   NULL |
|       NULL | ttttt    | NULL       | NULL  | NULL     |    2 |   NULL |
|       NULL | newNane1 | NULL       | NULL  | NULL     |    1 |    100 |
|       NULL | newName2 | NULL       | NULL  | NULL     |    3 |     80 |
|       NULL | newName3 | NULL       | NULL  | NULL     |    5 |     10 |
+------------+----------+------------+-------+----------+------+--------+
9 rows in set (0.00 sec)

mysql> select * from test order by salary;
+------------+----------+------------+-------+----------+------+--------+
| sal_salart | ename    | testColumn | birth | hiredate | age  | salary |
+------------+----------+------------+-------+----------+------+--------+
|       NULL | def      | NULL       | NULL  | NULL     |   81 |   NULL |
|       NULL | aaaaa    | NULL       | NULL  | NULL     |    3 |   NULL |
|       NULL | aaaaa    | NULL       | NULL  | NULL     |    5 |   NULL |
|       NULL | ccccc    | NULL       | NULL  | NULL     |    3 |   NULL |
|       NULL | aaaaa    | NULL       | NULL  | NULL     |    9 |   NULL |
|       NULL | ttttt    | NULL       | NULL  | NULL     |    2 |   NULL |
|       NULL | newName3 | NULL       | NULL  | NULL     |    5 |     10 |
|       NULL | newName2 | NULL       | NULL  | NULL     |    3 |     80 |
|       NULL | newNane1 | NULL       | NULL  | NULL     |    1 |    100 |
+------------+----------+------------+-------+----------+------+--------+
9 rows in set (0.00 sec)

mysql> select * from test order by salary limit 6,3;
+------------+----------+------------+-------+----------+------+--------+
| sal_salart | ename    | testColumn | birth | hiredate | age  | salary |
+------------+----------+------------+-------+----------+------+--------+
|       NULL | newName3 | NULL       | NULL  | NULL     |    5 |     10 |
|       NULL | newName2 | NULL       | NULL  | NULL     |    3 |     80 |
|       NULL | newNane1 | NULL       | NULL  | NULL     |    1 |    100 |
+------------+----------+------------+-------+----------+------+--------+
3 rows in set (0.00 sec)
 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值