Monthly Archives: 九月 2015

MySQL基础操作练习(所属的库叫做testdb)： 新建如下表（包括结构和内容）： ID Name Age Gender Course 1 zhangs 24 Male shuxue 2 lisi 19 Female yingyu 3 wangwu 18 Female yuwen 4 zhaoliu 52 Male meishu 5 Chenqi 22 Male xiezuo 2、完成如下操作 (1)找出性别为女性的所有人； (2)找出年龄大于20的所有人； (3)修改lisi的Course为gaoshu; (4)删除年龄小于等于19岁的所有人； (5)创建此表及所属的库； (6)授权给testuser对testdb库有所有访问权限； ******************************************************** 具体操作： [root@localhost ~]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 5.0.22 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql> show databases; +——————–+ | Database | +——————–+ | information_schema | | abc | | mysql | | test | +——————–+ 4 rows in set (0.00 sec) mysql> create database testdb; Query OK, 1 row affected (0.05 sec) mysql> show databases; +——————–+ | Database | +——————–+ | information_schema | | abc | | mysql | | test | | testdb | +——————–+ 7 rows in set (0.00 sec) mysql> use testdb; Database changed mysql> create table info(ID char(2) not null,Name char(25),Age tinyint,Gender char(10),Course char(50)); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +——————+ | Tables_in_testdb | +——————+ | info | +——————+ 1 row in set (0.00 sec) mysql> desc info; +——–+————+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——–+————+——+—–+———+——-+ | ID | char(2) | NO | | NULL | | | Name | char(25) | YES | | NULL | | | Age | tinyint(4) | YES | | NULL | | | Gender | char(10) | YES | | NULL | | | Course | char(50) | YES | | NULL | | +——–+————+——+—–+———+——-+ 5 rows in set (0.00 sec) mysql> alter table info add Addr char(10); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc info; +——–+————+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——–+————+——+—–+———+——-+ | ID | char(2) | NO | | NULL | | | Name | char(25) | YES | | NULL | | | Age | tinyint(4) | YES | | NULL | | | Gender | char(10) | YES | | NULL | | | Course | char(50) | YES | | NULL | | | Addr | char(10) | YES | | NULL | | +——–+————+——+—–+———+——-+ 6 rows in set (0.00 sec) mysql> insert into info value (“1″,”wang”,20,”nv”,”shuxue”,”henan”); Query OK, 1 row affected (0.00 sec) mysql> insert into info value (“2″,”yang”,30,”nan”,”english”,”shanxi”),(“3″,”liu”,60,”nv”,”maobi”,”gansu”); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from info; +—-+——+——+——–+———+——–+ | ID | Name | Age | Gender | Course | Addr | +—-+——+——+——–+———+——–+ | 1 | wang | 20 | nv | shuxue | henan | | 2 | yang | 30 | nan | english | shanxi | | 3 | liu | 60 | nv | maobi | gansu | +—-+——+——+——–+———+——–+ 3 rows in set (0.00 sec) mysql> select * from info where Gender=’nv’; +—-+——+——+——–+———+——-+ | ID | Name | Age | Gender | Course | Addr | +—-+——+——+——–+———+——-+ | 1 | wang | 20 | nv | shuxue | henan | | 3 | liu | 60 | nv | maobi | gansu | +—-+——+——+——–+———+——-+ 2 rows in set (0.00 sec) mysql> select * from info where Age>25 ; +—-+——+——+——–+———+——–+ | ID | Name | Age | Gender | Course | Addr | +—-+——+——+——–+———+——–+ | 2 | yang | 30 | nan | english | shanxi | | 3 | liu | 60 | nv | maobi | gansu | +—-+——+——+——–+———+——–+ 2 rows in set (0.00 sec) mysql> update info set Course=”wahaha” where Name=”liu”; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from info; +—-+——+——+——–+———+——–+ | ID | Name | Age | Gender | Course | Addr | +—-+——+——+——–+———+——–+ | 1 | wang | 20 | nv | shuxue | henan | | 2 | yang | 30 | nan | english | shanxi | | 3 | liu | 60 | nv | wahaha | gansu | +—-+——+——+——–+———+——–+ 3 rows in set (0.00 sec) mysql> delete from info where Age select * from info; +—-+——+——+——–+———+——–+ | ID | Name | Age | Gender | Course | Addr | +—-+——+——+——–+———+——–+ | 2 | yang | 30 | nan | english | shanxi | | 3 | liu | 60 | nv | wahaha | gansu | +—-+——+——+——–+———+——–+ 2 rows in set (0.00 sec) mysql> grant all privileges on testdb.info to testuser identified by “oktest”; Query OK, 0 rows affected (0.00 sec) mysql> show grants for testuser ; +——————————————————————————–+ | Grants for testuser@% | +——————————————————————————–+ | GRANT USAGE ON *.* TO ‘testuser’@'%’ IDENTIFIED BY PASSWORD ’14aac2b4059e6413′ | | GRANT ALL PRIVILEGES ON `testdb`.`info` TO ‘testuser’@'%’ | +——————————————————————————–+ 2 rows in set (0.00 sec) mysql> \q Bye Continue reading →