首页 > 代码库 > 常用sql commands
常用sql commands
1. mysql -uroot -p
2. show databases;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ccpdev | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
3.exit;
4. create database intrepid_detectives; // 创建新的database
5. use intrepid_detectives; // 开始使用新创的那个数据库
database stores their data in tables.数据库将数据保存在table中，一个database可以有多张表,就像一个execel spreadsheet可以有多个sheet一样的概念。
6. show tables;
mysql> use intrepid_detectives; Database changed mysql> show tables; Empty set (0.00 sec)
mysql> use mysql Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec)
从上面可以看出mysql默认builtin database内置有很多table
Every table has a set of columns, 每一张表都有一系列的columns(列).每一列都有一个name和一个datatype(at least).
7. create table <name> (<col-name> <col-type>,<col-name> <col-type>)
mysql> use intrepid_detectives; Database changed mysql> create table investigations ( -> title varchar(100), -> detective varchar(30), -> daysToSolve integer); Query OK, 0 rows affected (0.04 sec) mysql> show tables; +-------------------------------+ | Tables_in_intrepid_detectives | +-------------------------------+ | investigations | +-------------------------------+ 1 row in set (0.00 sec)
8. explain investigations; // 可以列出table的结构来：
mysql> explain investigations; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | title | varchar(100) | YES | | NULL | | | detective | varchar(30) | YES | | NULL | | | daysToSolve | int(11) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
9. insert into investigations values("Finnigan‘s Falut","Carlotta McOwen", 4); //插入一行
table中的每一个entry被称为record. 一个table可以有多个records. intersection of a column and a record is a field.
10. insert into investigations(datasToSolve, title,detective) values(3, "The Missing Tree", "Finch Hosky"); // 插入一行数据时，指定字段名称，不和数据库表中的字段顺序一致的办法
mysql> insert into investigations values("Finnigan‘s Falut","Carlotta McOwen", 4); Query OK, 1 row affected (0.00 sec) mysql> insert into investigations(daysToSolve, title,detective) values(3, "The Missing Tree", "Finch Hosky"); Query OK, 1 row affected (0.00 sec)
11. select * from investigations; // 从investigations表中获取所有数据
mysql> select * from investigations; +------------------+-----------------+-------------+ | title | detective | daysToSolve | +------------------+-----------------+-------------+ | Finnigan‘s Falut | Carlotta McOwen | 4 | | The Missing Tree | Finch Hosky | 3 | | ohter ing Tree | sssf | 3 | | ohter ing Tree | sssf | NULL | +------------------+-----------------+-------------+ 4 rows in set (0.00 sec)
NULL is the SQL value for "no value";任何一个字段都可能是NULL，只要你不给他赋值！
12. select title, detective from investigations; // 只选择部分字段
13. data types:
- varchar(100) create table person(name varchar(100));
- text create table email(body text);
- Numbers int/integer: create table person(age integer unsigned); // 正整数ff
- unsigned int: 0-4294967295
- BIGINT
- SMALLINT
- MEDIUMINT
- TINYINT
- decimal(precision, scope)比如decimal(10,0)=>0123546789; decimal(5,2)=>123.45;decimal(9,7)=>89.1234567
- auto_increment: 如果不给这个字段一个明确的值，则自动增1 create table student id integer auto_increment);
- date,time,datetime
- date: ‘YYYY-MM-DD‘;
- time: ‘[H]HH:MM:SS‘
- datetime: ‘YYYY-MM-DD HH:MM:SS‘ create table order(order_date date);
- Booleans: bool/boolean: tinyint(1) create table order(fulfilled boolean); 0: false, no-0 is true
- default values:
create table order(coupon varchar(10) default "nodiscount", customer_id integer default null, datetime datetime default current_timestamp,fulfilled boolean NT NULL default 0);
每一个数据库table都必须有一个primary key, a column that quniquely identifies each row. it can ndeve be null and must be set on record creation and never changed.
14. 创建constraint (主键)
mysql> create table detectives ( -> id int not null auto_increment, -> name varchar(100), -> phone_number varchar(10), -> certificationDate date, -> constraint detectives_pk primary key (id)); Query OK, 0 rows affected (0.05 sec) mysql> explain -> detectives; +-------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | phone_number | varchar(10) | YES | | NULL | | | certificationDate | date | YES | | NULL | | +-------------------+--------------+------+-----+---------+----------------+
mysql> insert into detectives(name,phone_number,certificationDate) -> values("zhangsan",12333,"2002-01-03"); Query OK, 1 row affected (0.00 sec) mysql> select * from detectives; +----+----------+--------------+-------------------+ | id | name | phone_number | certificationDate | +----+----------+--------------+-------------------+ | 1 | zhangsan | 12333 | 2002-01-03 | +----+----------+--------------+-------------------+ 1 row in set (0.00 sec)
15. alter table investigations rename cases; // 修改表格名称
mysql> alter table investigations rename cases; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-------------------------------+ | Tables_in_intrepid_detectives | +-------------------------------+ | cases | | detectives | +-------------------------------+ 2 rows in set (0.00 sec) mysql>
16. alter table cases add criminal varchar(100) // 增加一列, drop criminal则删除一列
mysql> alter table cases add criminal varchar(100); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from cases; +------------------+-----------------+-------------+----------+ | title | detective | daysToSolve | criminal | +------------------+-----------------+-------------+----------+ | Finnigan‘s Falut | Carlotta McOwen | 4 | NULL | | The Missing Tree | Finch Hosky | 3 | NULL | | ohter ing Tree | sssf | 3 | NULL | | ohter ing Tree | sssf | NULL | NULL | +------------------+-----------------+-------------+----------+ 4 rows in set (0.00 sec)
17. alter table detectives change certificationDate certification_date date //更改 old certificationDate字段名称为new: certificate_date，type不变!!
mysql> alter table detectives change certificationDate certification_date date; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain detectives; +--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | phone_number | varchar(10) | YES | | NULL | | | certification_date | date | YES | | NULL | | +--------------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
18. alter table cases add id int not null;
alter table cases add primary key (id); // 注意这时由于id默认为0，多个record都具有相同的id，因此这条命令会失败！！！
mysql> select * from cases; +------------------+-----------------+-------------+----+ | title | detective | daysToSolve | id | +------------------+-----------------+-------------+----+ | Finnigan‘s Falut | Carlotta McOwen | 4 | 0 | | The Missing Tree | Finch Hosky | 3 | 0 | | ohter ing Tree | sssf | 3 | 0 | | ohter ing Tree | sssf | NULL | 0 | +------------------+-----------------+-------------+----+ 4 rows in set (0.00 sec) mysql> alter table cases add primary key (id); ERROR 1062 (23000): Duplicate entry ‘0‘ for key ‘PRIMARY‘
解决方案：需要手工或者程序将该列的值更改为不同的整数，随后才能够成功
下面是手工修正id为不同值后能够正确执行add prmiary key(id)和change id id int not null auto_increment的过程
mysql> select * from cases; +------------------+-----------------+-------------+----+ | title | detective | daysToSolve | id | +------------------+-----------------+-------------+----+ | Finnigan‘s Falut | Carlotta McOwen | 4 | 1 | | The Missing Tree | Finch Hosky | 3 | 2 | | ohter ing Tree | sssf | 3 | 3 | | ohter ing Tree | sssf | NULL | 4 | +------------------+-----------------+-------------+----+ 4 rows in set (0.00 sec) mysql> alter table cases add primary key(id); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> explain cases; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | title | varchar(100) | YES | | NULL | | | detective | varchar(30) | YES | | NULL | | | daysToSolve | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | +-------------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table cases change id id INT NOT NULL AUTO_INCREMENT; Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> explain cases; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | title | varchar(100) | YES | | NULL | | | detective | varchar(30) | YES | | NULL | | | daysToSolve | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | +-------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
19. delete from criminals; // 删除整个criminals表格的数据
mysql> select * from criminals -> ; +-----------+ | name | +-----------+ | crimianl1 | | crimianl2 | | crimianl3 | +-----------+ 3 rows in set (0.00 sec) mysql> delete from criminals; Query OK, 3 rows affected (0.00 sec) mysql> select * from criminals; Empty set (0.00 sec)
常用sql commands
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。